need help with case in case, but this dont work:

have this :

SELECT *,
       CASE
         WHEN scorecard_id = '4.2.2' then (
           CASE
             WHEN xpav = '110_0_0_0_email_extension' AND xval = 'com' THEN '77.187'
             ELSE '49.595'
           END ) when
           (
           CASE
             WHEN xpav = '13_0_0_0_age' AND xval BETWEEN '18' AND '23' THEN '1.829'
             ELSE '49.595'
           END )) end

I need

IF   
scorecard_id = '4.2.2' 
and  xpav = '110_0_0_0_email_extension' 
AND xval = 'com' 
then '77.187' else '20.058'


if scorecard_id = '4.2.2' 
and xpav = '13_0_0_0_age' AND xval BETWEEN '18' AND '23' 
THEN '1.829'

if scorecard_id = '4.2.2' 
xpav = '13_0_0_0_age' AND xval BETWEEN '24' AND '26' 
then '49.061'
else 49.595

I have a lot of criteria, but I want at least the very beginning so I can understand and continue to know how to do it

CodePudding user response：

you need to have another condition for your 2nd case expression.

case 
    when scorecard_id='4.2.2' and xpav = '110_0_0_0_email_extension' and xval = 'com' then '77.187' 
    when scorecard_id = '4.2.2' and xpav = '13_0_0_0_age' and xval between 18 and 23 then '1.829'
    when scorecard_id = '4.2.2' and xpav = '13_0_0_0_age' and xval between 24 and 26 then '49.061'
    when scorecard_id = '4.2.2' and xpav = '13_0_0_0_age' and xval 
        not between 24 and 26 or xval not between 18 and 23 then '49.061'
else '20.058'
end