Hi there,

I'm having problems with the following query, apparently in the CASE as system shows me the following error message :
''The search-condition in a SEARCH-WHEN-CLAUSE is not valid.''

SELECT SEC_COV_ID, PAR_SEC_PLG_FL,
(CASE WHEN OPS_RTG_AGT_ID ='UNR' THEN 'UNRATED'
WHEN OPS_RTG_ID IN ('A1','A2','A3','AAA','AA','A','BBB')
THEN 'INVESTMENT GRADE'
WHEN OPS_RTG_ID IN ('A4','A5','A6','BB','B','CCC','CC','C','D')
THEN 'NON INVESTMENT GRADE'
ELSE OPS_RTG_ID END) AS SECRTG,
SUM(PAR_SEC_POS_COL_AM), SUM(PAR_SEC_POS_MKV_AM),
SUM(PAR_SEC_POS_COL_AM)/SUM(PAR_SEC_POS_MKV_AM)
FROM PDB2I.DI_PAR_ACC_01C A,
PDB2I.RI_PAR_SEC_POS_01 B,
PDB2I.DI_OPR_01 C,
PDB2I.DI_SEC_STB_01 D
WHERE A.PAR_ACC_NB=B.PAR_ACC_NB
AND B.SEC_ID=D.SEC_ID
AND C.OPR_ID=A.PAR_ACC_NB
AND SEC_COV_ID IN ('CDO','ABS','CMBS','RMBS')
AND SEC_CAN_DT='0001-01-01'
AND SEC_HLD_FL='Y'
AND SEC_MAT_DT >CURRENT DATE
AND PAR_SEC_POS_TYP_AB='SB'
GROUP BY SEC_COV_ID, PAR_SEC_PLG_FL,
OPS_RTG_AGT_ID, OPS_RTG_ID

Many thanks in advance for providing me with the correct script!

Kind regards,

PierreD

Resolution
(by the way, I can't believe that I have to write it down like this >_<)

SELECT SEC_COV_ID, PAR_SEC_PLG_FL,
(CASE WHEN OPS_RTG_AGT_ID ='UNR' THEN 'UNRATED'
WHEN OPS_RTG_ID = 'A1' OR OPS_RTG_ID ='A2'
OR OPS_RTG_ID ='A3'
OR OPS_RTG_ID ='AAA'
OR OPS_RTG_ID ='AA'
OR OPS_RTG_ID ='A'
OR OPS_RTG_ID ='BBB'
THEN 'INVESTMENT GRADE'
WHEN OPS_RTG_ID = 'A4' OR OPS_RTG_ID ='A5'
OR OPS_RTG_ID ='A6'
OR OPS_RTG_ID ='BB'
OR OPS_RTG_ID ='B'
OR OPS_RTG_ID ='CCC'
OR OPS_RTG_ID ='CC'
OR OPS_RTG_ID ='C'
OR OPS_RTG_ID ='D'
THEN 'NON INVESTMENT GRADE'
ELSE OPS_RTG_ID END) AS SECRTG,
SUM(PAR_SEC_POS_COL_AM), SUM(PAR_SEC_POS_MKV_AM),
SUM(PAR_SEC_POS_COL_AM)/SUM(PAR_SEC_POS_MKV_AM)
FROM PDB2I.DI_PAR_ACC_01C A,
PDB2I.RI_PAR_SEC_POS_01 B,
PDB2I.DI_OPR_01 C,
PDB2I.DI_SEC_STB_01 D
WHERE A.PAR_ACC_NB=B.PAR_ACC_NB
AND B.SEC_ID=D.SEC_ID
AND C.OPR_ID=A.PAR_ACC_NB
AND SEC_COV_ID IN ('CDO','ABS','CMBS','RMBS')
AND SEC_CAN_DT='0001-01-01'
AND SEC_HLD_FL='Y'
AND SEC_MAT_DT >CURRENT DATE
AND PAR_SEC_POS_TYP_AB='SB'
GROUP BY SEC_COV_ID, PAR_SEC_PLG_FL,
OPS_RTG_AGT_ID, OPS_RTG_ID

If you have an other way to structure the query, I would be glad to read it.

Thanks!

High PierreD,

what's your DB2 version and system ?

Normally, CASE combined with
column IN ( value,value..)
should work.

What is the exact error message ?

I see a possible inconsistence (not matching columns) between your SELECT clause and your GROUP BY.

SELECT SEC_COV_ID, PAR_SEC_PLG_FL, (expression) AS SECRTG, SUM(),SUM(),SUM()
GROUP BY SEC_COV_ID, PAR_SEC_PLG_FL, OPS_RTG_AGT_ID, OPS_RTG_ID

Hi,

I have no idea about version and system but the above query works now, it's just the fact that the 'WHEN' condition doesn't accept 'IN', so I had to find a way around and the list of 'OR' seems to be the only solution... :s

Concerning the grouping, I may not group by a user defined column i.e. SECRTG, so i had to group by what's in this user defined column i.e.
OPS_RTG_AGT_ID, OPS_RTG_ID

Thanks for having replied.

You should really figure out which version of DB2 you are running on which platform. Because what you describe works perfectly fine (on DB2 LUW):