Results 1 to 5 of 5

Thread: Problem with a

  1. #1
    Join Date
    Nov 2007
    Posts
    3

    Unanswered: Problem with a

    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

  2. #2
    Join Date
    Nov 2007
    Posts
    3
    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!

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    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

  4. #4
    Join Date
    Nov 2007
    Posts
    3
    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.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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):
    Code:
    $ db2 "values case when 'a' IN ( '1', 'a', '1398712', 'oaoecg' ) then 1 end"
    
    1
    -----------
              1
    
      1 record(s) selected.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •