Using case statements in queries, where and how?
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.