Nested or multiple case functions-CodePudding

I am trying to do a nested case when query. My table has ID, code1 and code2 columns. create table as select ID, code1 case when code1 is not null then 'yes' else (select code2, case when code 2 is not null then 'no' else 'NA' from table) from table

What I want to achieve is that if code1 is null, I want to go ahead and check for code2 being null or not null. If code1 is not null then it gets a tag 'yes' right away without any checks on code2. What would be the right way to do this. Any help would be appreciated. Thanks.

CodePudding user response：

You only need 1 CASE expression:

SELECT ID, code1,
       CASE 
         WHEN code1 IS NOT NULL THEN 'yes' 
         WHEN code2 IS NOT NULL THEN 'no'
         ELSE 'NA'
       END AS tag
FROM tablename

Each WHEN branch is processed only if the previous ones failed.

CodePudding user response：

Combined with creating a table, you get the id and a tag with your specification

CREATE TABLE mynewtable SELECT ID,
    CASE
        WHEN code1 IS NOT NULL THEN 'yes'
        WHEN code2 IS NOT NULL THEN 'no'
        ELSE 'NA'
    END tag FROM
    table1