CASE query clarification

New to SQL and I've just created a SQL query with assitance:

       SELECT CASE

    WHEN exists (SELECT CLIENT_CODE FROM STG_DM_CLIENT      
       WHERE CLIENT_CODE 
      NOT IN (SELECT CLIENT_CODE FROM DM_CLIENT)) THEN 'A'
      else

WHEN  exists (SELECT STG.CLIENT_CODE AS TRAN_TYPE
    FROM  STG_DM_CLIENT STG          
    JOIN DM_CLIENT SRC
    ON SRC.CLIENT_CODE = STG.CLIENT_CODE  
    WHERE (SRC.CLIENT_NAME <> STG.CLIENT_NAME)) THEN 'C'
    END 
FROM DM_CLIENT, STG_DM_CLIENT

Fails to provide the correct resulset, states incorect syntax next to when

Msg 156, Level 15, State 1, Lin开发者_StackOverflowe 8 Incorrect syntax near the keyword 'WHEN'.

Any ideas as to what I'm doing wrong

You only want to have an ELSE statement at the END. Proper format is something like:

CASE WHEN <condition 1> THEN <output 1>
     WHEN <condition 2> THEN <output 2>
     -- Optional, if left out assumes NULL for unhandled conditions
     [ELSE <output 3> ]
     END

When you put the ELSE after the first conditional the engine expects a final output, not another condition.

Let me format your example (without modificiation), so that the structure becomes visible:

SELECT CASE WHEN exists (SELECT CLIENT_CODE FROM STG_DM_CLIENT       
                         WHERE CLIENT_CODE NOT IN (SELECT CLIENT_CODE FROM DM_CLIENT))
            THEN 'A' 
            else WHEN  exists (SELECT STG.CLIENT_CODE AS TRAN_TYPE 
                                 FROM STG_DM_CLIENT STG           
                                      JOIN DM_CLIENT SRC 
                                      ON SRC.CLIENT_CODE = STG.CLIENT_CODE   
                                WHERE (SRC.CLIENT_NAME <> STG.CLIENT_NAME))
                 THEN 'C' 
                 END  
  FROM DM_CLIENT, STG_DM_CLIENT 

So your structure is:

  CASE WHEN ... THEN ...
       ELSE WHEN ... THEN ...
            END

That's not correct. You can change it to either two nested case statements (complete with CASE and END):

  CASE WHEN ... THEN ...
       ELSE CASE WHEN ... THEN ...
            END
  END

or (better):

  CASE WHEN ... THEN ...
       WHEN ... THEN ...
       [ELSE ...]
  END

So, remove the else from your original statement and you should be fine.

It's not clear what your after, this is the closest valid statement;

SELECT 
    CASE WHEN EXISTS (...) 
        THEN 'A'
    ELSE 
        CASE WHEN EXISTS (...) 
            THEN 'C'
            ELSE 'Neither exists()'
        END
    END

When the 1st exists is matched you will get A
When the 1st does not exist you will get C if the 2nd does match