CODEWITHSUNDEEP
sqlsql-servercase
Iasha
Iasha

Reputation: 77

SQL Server Case Expression not working with second condition

I have sql server case expression that doesn't seem to work when it gets to this line:

 WHEN cl.Product_Type = 'PerformanceSwap' and 
 cl.Underlying_Product_Code_IndexType IS NULL THEN

The full block of code is:

CASE
WHEN prod_map.Product_ID1 = 'CR' AND prod_map.Product_Type <> 
'PerformanceSwap' THEN  
--(mandatory for transactions where asset_class = CR)
   CASE 
     WHEN cl.AB_Payment_Frequency = 'Monthly' THEN 'MNTH'
     WHEN cl.AB_Payment_Frequency = 'Quarterly' THEN 'QURT'
     WHEN cl.AB_Payment_Frequency = 'Yearly' THEN 'YEAR'

         WHEN cl.Product_Type = 'PerformanceSwap' and 
         cl.Underlying_Product_Code_IndexType IS NULL THEN
            CASE
            WHEN cl.AB_Finance_Payment_Frequency = 'MNTH' THEN 'MNTH'
            WHEN cl.AB_Finance_Payment_Frequency = 'SW' THEN 'MIAN'
            WHEN cl.AB_Finance_Payment_Frequency = 'QTR' THEN 'QURT'
            WHEN cl.AB_Finance_Payment_Frequency = 'PA' THEN 'YEAR'
            WHEN cl.AB_Finance_Payment_Frequency = 'ZC' THEN 'YEAR'
ELSE ''
END
END
END,

Do I need to do another case expression within this? or something else so it can up the AB_Finance_Payment_Frequency values.

Upvotes: 0

Views: 169

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

I am guessing that you are overnesting the case expressions.

The logic for the two "outer" WHEN comparisons are incompatible -- just look at the Product_Type logic. The second should follow the first rather than be nested.

This may be what you want:

(CASE WHEN prod_map.Product_ID1 = 'CR' AND prod_map.Product_Type <> 'PerformanceSwap'
      THEN  --(mandatory for transactions where asset_class = CR)
            (CASE WHEN cl.AB_Payment_Frequency = 'Monthly' THEN 'MNTH'
                  WHEN cl.AB_Payment_Frequency = 'Quarterly' THEN 'QURT'
                  WHEN cl.AB_Payment_Frequency = 'Yearly' THEN 'YEAR'
             END)
      WHEN cl.Product_Type = 'PerformanceSwap' and cl.Underlying_Product_Code_IndexType IS NULL
      THEN (CASE WHEN cl.AB_Finance_Payment_Frequency = 'MNTH' THEN 'MNTH'
                 WHEN cl.AB_Finance_Payment_Frequency = 'SW' THEN 'MIAN'
                 WHEN cl.AB_Finance_Payment_Frequency = 'QTR' THEN 'QURT'
                 WHEN cl.AB_Finance_Payment_Frequency = 'PA' THEN 'YEAR'
                 WHEN cl.AB_Finance_Payment_Frequency = 'ZC' THEN 'YEAR'
                 ELSE ''
            END)
 END)

However, I notice that you still have only one ELSE clause. I suspect that the logic you need might be a bit different. You should map it out and then implement it.

I would also encourage you to indent the code very carefully so you can easily follow the logic.

Upvotes: 2

Related Questions