Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Using case statements in queries, where and how?

costumerMar 4 2009 — edited Mar 4 2009

I am trying to write a query that returns address fields according to a type field within the address table. There is a hierarchy to the return order, 'M CO' first, then 'M', then 'D'. I had been using left outer joins to potentially pull three sets of fields back from the database (there will ALWAYS be a D type, but not necessarily an M or an M CO) then using if statements within the program to write out the proper address. However, my supervisor wants me to use case statements to pull back only one set of address fields within the query. I'm at a total loss. I have used case statements before, but not on something like this.

Here is the code I HAD been using:

.
.
.
AD.ADDRESS1,
AD.ADDRESS2,
AD.CITY,
AD.STATE,
AD.ZIP,
AD.PHONE1,
MAD.ADDRESS1 AS MAILING_ADDRESS1,
MAD.ADDRESS2 AS MAILING_ADDRESS2,
MAD.CITY AS MAILING_CITY,
MAD.STATE AS MAILING_STATE,
MAD.ZIP AS MAILING_ZIP,
MAD.PHONE1 AS MAILING_PHONE1,
CAD.ADDRESS1 AS ADDRESS1,
CAD.ADDRESS2 AS ADDRESS2,
CAD.CITY AS CITY,
CAD.STATE AS STATE,
CAD.ZIP AS ZIP,
CAD.PHONE1 AS PHONE1,
.
.
.
LEFT OUTER JOIN ADDRESS AD ON ((M.CONTRACT_NBR = AD.ADDNO)
AND (AD.ADDTYPE = 'D')
AND (AD.WHOTYPE = 'S')
AND (V_RUNDATE BETWEEN AD.YMDEFF AND AD.YMDEND)
AND (AD.VOID = ' '))
LEFT OUTER JOIN ADDRESS MAD ON ((M.CONTRACT_NBR = MAD.ADDNO)
AND (MAD.ADDTYPE = 'M')
AND (MAD.WHOTYPE = 'S')
AND (V_RUNDATE BETWEEN MAD.YMDEFF AND MAD.YMDEND)
AND (MAD.VOID = ' '))
LEFT OUTER JOIN ADDRESS CAD ON ((M.CONTRACT_NBR = CAD.ADDNO)
AND (CAD.ADDTYPE = 'M CO')
AND (CAD.WHOTYPE = 'S')
AND (V_RUNDATE BETWEEN CAD.YMDEFF AND CAD.YMDEND)
AND (CAD.VOID = ' '))
.
.
.

I am confused about both how to word the case statement, and where to use it, either in the select list, or the where clause.

This post has been answered by 667579 on Mar 4 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 1 2009
Added on Mar 4 2009
6 comments
540 views