Need Assistance With DB2 SQL Query

Question

I'm working on a DB2 Query that I need assistance with. If someone is willing to take a look I'd really appreciate it.

The query below is what I have so far:

SELECT
 C.CONTROL_NO,
    NR.NOTICE_CODE,
    COALESCE(C.LNAME,'') AS LNAME,
    COALESCE(C.FNAME,'') AS FNAME,
 CASE
    WHEN SOC_SEC_NO IS NULL THEN ''
       WHEN SOC_SEC_NO IS NOT NULL THEN REPLACE(SOC_SEC_NO, LEFT(SOC_SEC_NO,5) , '*****')
    END AS SOC_SEC_NO,
    COALESCE(C.SOC_SEC_NO, '') AS SOC_SEC_NO_FULL,
    COALESCE(C.EMPLOYER,'') AS EMPLOYER,
    COALESCE(C.FED_EMP_NO, '') AS FED_EMP_NO,
    COALESCE(CAST(C.STW_DATE AS VARCHAR(10)),'') AS STW_DATE,
    COALESCE(CAST(C.CREATE_DTE AS VARCHAR(10)),'') AS CREATE_DTE,
    CASE WHEN C.STATUS IS NULL THEN ''
       WHEN C.STATUS = 'CER - CERTIFIED' THEN 'CERTIFIED'
       WHEN C.STATUS = 'DEF - DEFICIENT' THEN 'DEFICIENT'
       WHEN C.STATUS = 'DEN - DENIED' THEN 'DENIED'
       WHEN C.STATUS = 'D90 - DENIED(OVER 90 DAYS OLD)' THEN 'DENIED(OVER 90 DAYS OLD)'
       WHEN C.STATUS = 'EPA - POA EXPIRED' THEN 'POA EXPIRED'
       WHEN C.STATUS = 'EXP - EXPIRED REQUEST' THEN 'EXPIRED REQUEST'
       WHEN C.STATUS = 'MAN - MANUAL' THEN 'MANUAL'
       WHEN C.STATUS = 'NAR - NON ACTIONABLE REQUEST' THEN 'NON ACTIONABLE REQUEST'
       WHEN C.STATUS = 'NPA - POA NOT FOUND' THEN 'POA NOT FOUND'
       WHEN C.STATUS = 'NPC - PENDING CERT/NO POA' THEN 'PENDING CERT/NO POA'
       WHEN C.STATUS = 'NPD - PENDING DENY/NO POA' THEN 'PENDING DENY/NO POA'
       WHEN C.STATUS = 'OOS - OOS VALIDATION' THEN 'OOS VALIDATION'
       WHEN C.STATUS = 'OTG - OTHER TARGET GROUPS' THEN 'OTHER TARGET GROUPS'
       WHEN C.STATUS = 'PEN - PENDING VALIDATION' THEN 'PENDING VALIDATION'
       WHEN C.STATUS = 'RTV - READY TO VALIDATE' THEN 'READY TO VALIDATE'
    END AS STATUS,
    CASE
    WHEN C.WOTC_AWARD_CODE IS NULL AND WTW_AWARD_CODE IS NULL THEN ''
    WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'A  ' THEN 'A - SHORT-TERM TANF RECIPIENT' 
    WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'B  ' THEN 'B - VETERANS' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'BA ' THEN 'BA - QUALIFIED VET RECEIVING SNAP BENEFITS'
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'BB ' THEN 'BB - DISABLED VET'  
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'BC ' THEN 'BC - DISABLED VET UNEMPLOYED FOR 6 MONTHS' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'BD ' THEN 'BD - VET UNEMPLOYED FOR 4 WEEKS BUT LESS THAN 6 MONTHS' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'BE ' THEN 'BE - VET UNEMPLOYED FOR 6 MONTH' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'C  ' THEN 'C - EX-FELON' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'D  ' THEN 'D - DESIGNATED COMMUNITY RESIDENT' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'E  ' THEN 'E - VOCATIONAL REHABILITATION REFERRAL' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'F  ' THEN 'F - SUMMER YOUTH' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'G  ' THEN 'G - SNAP (FOOD STAMP) RECIPIENT' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'H  ' THEN 'H - SUPPLEMENTAL SECURITY INCOME (SSI) RECIPIENT' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = '  I' THEN 'I - LONG-TERM TANF RECIPIENT' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'J  ' THEN 'J - UNEMPLOYED VETERAN' 
       WHEN CONCAT(C.WOTC_AWARD_CODE, C.WTW_AWARD_CODE) = 'K  ' THEN 'K - DISCONNECTED YOUTH' 
    END AS WOTC_AWARD_CODE,  
 CASE
    WHEN C.STATUS IN ('CER - CERTIFIED', 'DEF - DEFICIENT', 'DEN - DENIED', 'D90 - DENIED(OVER 90 DAYS OLD)', 'EXP - EXPIRED REQUEST', 'NAR - NON ACTIONABLE REQUEST')
          THEN COALESCE(CAST(NR.NOTICE_DT AS VARCHAR(10)), '')
       ELSE ''
 END AS NOTICE_DT
FROM WITC00DS.DB2CUST C
LEFT OUTER JOIN WITC00DS.NOTIFICATION_REQUE NR
 ON C.CONTROL_NO = NR.CONTROL_NBR 
WHERE
 C.AGENT_CODE = 'HR'
AND C.STW_DATE BETWEEN '01/01/2000' AND '01/21/2014' 
AND (
   (NR.NOTICE_DT = (SELECT MAX(NOTICE_DT) FROM WITC00DS.NOTIFICATION_REQUE ZZ WHERE ZZ.CONTROL_NBR = C.CONTROL_NO AND NOTICE_CODE IN ('C', 'F', 'D', 'D2', 'XP', 'S', 'VR'))
      OR (NR.NOTICE_DT IS NULL))
 )
AND
   ((NOTICE_CODE IN ('C', 'F', 'D', 'D2', 'XP', 'S', 'VR') 
   ) OR (NR.NOTICE_DT IS NULL))
ORDER BY Control_No 

As can be seen this query basically does a left outer join between two tables: DB2CUST and NOTIFICATION_REQUE on the control number column in both tables.

This query is very close to what I need however in a very limited number of cases I get duplicate control number records in the results where the maximum notice dates are equal. In the few cases where the notice dates are equal I need to select only one that fits the following criteria:

1. If the status in DB2CUST = 'Deficient' and notice_code = 'F' select that record
2. If the status in DB2CUST = 'Denied' and notice_code = 'D' select that record
3. If the status in DB2CUST = 'Certified' and notice_code = 'C' select that record etc.

So, in the very few cases where there are duplicate max notice dates I need to select one of the two records based on the correct combination of status and notice code shown above. Can someone help me enhance the query I currently have to do this?

Thanks,

Pete

Answer 1

Thanks for the help. I really appreciate it. I was able to get it to run but had to make a few minor changes. The final result is shown below:

WITH CTE AS  
( <My Query> )
    SELECT * FROM 
    (
   SELECT  CTE.*, ROW_NUMBER() OVER 
    (
      PARTITION BY CONTROL_NO ORDER BY 
      (
         CASE 
            WHEN STATUS = 'Deficient' AND NOTICE_CODE = 'F' THEN 1 
            WHEN STATUS = 'Denied' AND NOTICE_CODE = 'D' THEN 2 
            WHEN STATUS = 'Certified' AND NOTICE_CODE = 'C' THEN 3 
         END
      ) 
   ) AS SEQNUM
   FROM CTE 
) T  
WHERE SEQNUM = 1  

Also, I was wondering how can I sort the results DESCENDING by Control_No? Thanks again for your help.

Pete

Answer 2

You can get what you want using row_number(). Here is an example of what you can do:

with cte (<your query here>)
select cte.*
from (select cte.*,
             row_number() over (partition by CONTROL_NO order by
                                (case when DB2CUST.status = 'Deficient' and notice_code = 'F' then 1
                                      when DB2CUST.status = 'Denied' and notice_code = 'D' then 2
                                      when DB2CUST.status = 'Certified' and notice_code = 'C' then 3
                                      . . .
                                 end)
                                ) as seqnum

      from cte
     ) t
where seqnum = 1;