CODEWITHSUNDEEP
mysqlsqlsql-server
oni_aniq
oni_aniq

Reputation: 43

Nested case statement sql

I'm having issues with writing a case statement in SQL. My first question is: Is it possible to write the if statement below as a case statement in a SQL Query within the select statement?

If no, then please have a look at the case statement below and help/guide me to get into a valid format. Thanks and much appreciated! 

IF (var1 = 1){
do this1;
IF (var 1 = 2){
    Do this2;
    }Else{ 
    do something else1;
    }
Else if (Var 1 = 3){
    Do this3; 
    }Else{ 
    Do something else2;
    }

Here is my case statement. I know it doesn't work because it's not a valid case statement. Could someone kindly help me in making it a valid case statement. Thanks in advance.

    SELECT
CASE
    WHEN apple.type = 1 OR apple.type = 2
    THEN basket.S1
    ELSE 
        CASE 
            WHEN apple.type = 0 AND basket.S2 is null
            THEN basket.S1
            ELSE basket.S2  
        ELSE 
            CASE
                WHEN apple.type = 3 and basket.s3 is null
                THEN basket.S1
                ELSE basket.S3
            END
        END
    END 
FROM .....
WHERE .....

Upvotes: 4

Views: 1790

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26343

Since you're checking for nulls and substituting non-null values, you can make the query shorter by using the COALESCE function.

SELECT
  CASE
    WHEN apple.type IN (1, 2) THEN basket.s1
    WHEN apple.type = 0 THEN COALESCE(basket.s2, basket.s1)
    WHEN apple.type = 3 THEN COALESCE(basket.s3, basket.s1)
  END
FROM ...

Upvotes: 2

M.Ali
M.Ali

Reputation: 69524

I think you are over complicating your case statement , looking at your first example you case statement should be fairly simple,

something like ....

SELECT CASE 
           WHEN @Var1 = 1 THEN 'Something 1'
           WHEN @Var1 = 2 THEN 'Something 2'
           WHEN @Var1 = 3 THEN 'Something 3'
           ELSE 'Something Else'
       END 
FROM .....
WHERE .....

You case statement can we written something like this...

SELECT
    CASE
        WHEN apple.[type] IN (1,2)
        THEN basket.S1
        WHEN apple.type = 0 AND basket.S2 is null
        THEN basket.S1
        WHEN apple.type = 3 and basket.s3 is null
        THEN basket.S1
        ELSE basket.S3
    END

Upvotes: 4

Related Questions