Prioritizing values in select statement in ORACLE

Question

Due to the JOIN for the communication method (Phone, email etc) we get alot of duplicates because a company can have multiple communication methods (CICM.CLIENT_VALUE).

My question is... Is there a way to incorporate in the select statement to where if out of the client_Value field, if the value = EMAIL & Phone then only pull EMAIL for that CUSTOMER_ID, if they dont have have email and only phone, then pull phone

Right now I can have CUSTOMER_ID = 1 and have 2 records with the same data but the client_value will be different because they have an email, and phone, so I am getting 2 returned records instead of prioritizing 1 over the other.

Here is an image to show what I am referencing...you can see all the blacked out fields have the same value on both records but the contact_type and value are different. I would like to prioritize and choose PHONE over EMAIL if available, and not get BOTH to reduce record return count...

example of results

If this needs clarification please advise.

SELECT DISTINCT INV.CUSTOMER_ID
 , INV.ADDRESS_ID
 , INV.NAME
 , INV.ADDRESS1
 , INV.ADDRESS2
 , INV.CITY
 , INV.STATE
 , INV.ZIP_CODE
 , INV.COUNTRY
 , CICM.CLIENT_VALUE as CONTACT_TYPE
 , CICM.VALUE
 , CICM.DESCRIPTION
 , INV.REGION_CODE
 , INV.SITE
 , LD.LSTINVDATE
 , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-13') AND TO_DATE('30-Sep-15') THEN SUM(INV.SALES) ELSE 0 END AS TWO_YEARS
 , CASE WHEN TO_DATE(INV.INVDATE) BETWEEN TO_DATE('01-Oct-15') AND SYSDATE THEN SUM(INV.SALES) ELSE 0 END AS SALESPROGRESS
 , SUM(INV.SALES) AS TOTALSALES
FROM IFSINFO.HB_INVOICING_ALL INV 
LEFT JOIN IFSINFO.HB_LAST_INVOICE_DATE LD 
 ON (INV.COMPANY = LD.COMPANY) 
 AND (INV.CUSTOMER_ID = LD.IDENTITY)
LEFT JOIN IFSAPP.CUSTOMER_INFO_COMM_METHOD CICM
 ON (INV.ADDRESS_ID = CICM.ADDRESS_ID)
 AND (INV.CUSTOMER_ID = CICM.CUSTOMER_ID)
WHERE INV.CUST_GRP = '4'
AND INV.COMPANY = '20'
AND INV.STATE = '&State'
GROUP BY INV.COMPANY
 , INV.CUSTOMER_ID
 , INV.ADDRESS_ID
 , INV.NAME
 , INV.ADDRESS1
 , INV.ADDRESS2
 , INV.CITY
 , INV.STATE
 , INV.ZIP_CODE
 , INV.COUNTRY
 , CICM.CLIENT_VALUE
 , CICM.VALUE
 , CICM.DESCRIPTION
 , INV.REGION_CODE
 , INV.SITE
 , LD.LSTINVDATE 
 , INV.INVDATE
HAVING SUM(CASE WHEN INV.INVDATE >= TO_DATE('01-Oct-13')
 AND INV.INVDATE < TO_DATE('30-Sep-15')
 THEN INV.SALES ELSE 0 
 END) = 0

Answer 1

If I have well understood your question, you should use the DECODE function in your query in the SELECT and GROUP BY sections.

NB: for performance purpose, you might create an index using that function.

• Hector: Thank you for the reply Jefferson, I am going to play around with the Decode function to see if I can get this right! I will keep you guys posted.