sqlpostgresqlcase-whensubquerymultiple-conditions

Simplify nested case when statement


Below is my current SELECT CASE statement:

SELECT CASE 
WHEN edition = 'STAN' AND has9 = 1 THEN '9'
WHEN edition = 'STAN' AND has8 = 1 THEN '8'
WHEN edition = 'STAN' AND has7 = 1 THEN '7' 
WHEN edition = 'STAN' AND hasOLD = 1 THEN 'OLD'
WHEN edition = 'SUI'  AND has_s9 = 1 THEN 'S9' 
WHEN edition = 'SUI'  AND has_s8 = 1 THEN 'S8' ELSE 'S7' END AS version

I do not always want to repeat the edition = 'xxx' condition, such as

CASE WHEN edition = 'STAN' AND has9 = 1 THEN '9' ELSE WHEN has8 = 1 THEN '8' ELSE WHEN has7 = '7' ELSE WHEN edition 'SUI' AND has_s9 = 1 THEN 'S9' ELSE ...

In Excel this is fairly easy but how can I compile that in PostgreSQL?


Solution

  • Try this

    SELECT CASE 
    WHEN edition = 'STAN' THEN 
         CASE 
              WHEN has9 = 1 THEN '9'
              WHEN has8 = 1 THEN '8'
              WHEN has7 = 1 THEN '7'
              WHEN hasOLD = 1 THEN 'OLD'
         END
    WHEN edition = 'SUI' THEN
         CASE 
              WHEN has9 = 1 THEN 'S9'
              WHEN has8 = 1 THEN 'S8'
         END
    ELSE 'S7' END AS version