Wednesday, September 26, 2012

IBM DB2 - Using CASE in Select Query


Example:1

SELECT  CASE WHEN CM.CASE_TYPE='G' THEN 'Government' ELSE 'Non-Financial' END As CASE_TYPE_NAME ,
    CM.CASE_SEQUENCE_NUM,CS.DESC_EN,COUNT(*) AS TOTAL   
FROM
     FIU.CASES_MASTER CM,FIU.FI_SUSPECT_CASE_INFO SC,FIU.CASE_STATUS CS
WHERE    CM.CASE_SEQUENCE_NUM!=0 AND CM.CASE_SEQUENCE_NUM=SC.CASE_SEQUENCE_NUM AND CM.STATUS=CS.CODE
GROUP BY    CM.CASE_SEQUENCE_NUM,CS.DESC_EN,CASE WHEN CM.CASE_TYPE='G' THEN 'Government' ELSE 'Non-Financial' END


Example:2

SELECT DISTINCT CASE WHEN CM.CASE_TYPE='G' THEN 'Government'
        WHEN CM.CASE_TYPE='F' THEN 'Financial' 
          WHEN CM.CASE_TYPE='N' THEN 'Non-Financial' 
          WHEN CM.CASE_TYPE='S' THEN 'Sector' 
          WHEN CM.CASE_TYPE='I' THEN 'Individual' END As CASE_TYPE_NAME       
FROM
     FIU.CASES_MASTER CM

No comments:

Post a Comment