Issue with Case Condition in My SQL Query

Question

I have a sample table where I have written Case statement. where I'm not able to write the proper syntax.

SELECT 
    case
        when SECTION = 'A' then
            case
                WHEN (SECTION ='A' AND ROLE = ROLENUMBER) 
                THEN 'GOOD' 
                ELSE 'BAD'
            end
        else 
            case
                when SECTION <> 'A' and ROLE <> 0 and ROLE is not null and ROLENUMBER <> 0 and ROLENUMBER <> null and ROLENUMBER <> ROLE 
                    then 'GOOD' 
                    else  'BAD'  
            when SECTION <> 'A' and (ROLE = 0 or   ROLE IS null) and ROLENUMBER = ROLE 
                then 'GOOD' 
                else  'BAD'  
        end
FROM
    CLASSROOM

Apart from Case Condition is there anyother way to write like IF condition with in the Case condition.

I'm looking to write query like IF section = 'A' then execute 1st condition ELSE IF section <> 'A'then execute below condition.

You don't need to repeat `SECTION='A'` in the nested `CASE`. — Barmar, Mar 07 '23 at 17:04
You can't use `!=` with `NULL`. You have to write `ROLE IS NOT NULL` — Barmar, Mar 07 '23 at 17:05
At least one problem in your code is you're using `!= null` - which won't work in SQL, because in SQL a `NULL` cannot be compared to anything, even itself, i.e.: `( NULL != NULL ) => FALSE` and `( NULL == NULL ) => FALSE` - you have to use `IS NULL` or `IS NOT NULL` instead. — Dai, Mar 07 '23 at 17:05
The `ELSE CASE` probably doesn't need a nested case. Just make them two more `WHEN` clauses for the original `CASE`. And you don't need `SECTION != 'A'` since that's already handled by the first `WHEN`. — Barmar, Mar 07 '23 at 17:09
thanks @Barmar for suggestions . I'm getting syntax errors. Can you suggest in answer section — mohan111, Mar 07 '23 at 17:13
I'm having trouble figuring out the logic you're trying to implement. Can you explain all the "good" conditions? — Barmar, Mar 07 '23 at 17:15
section = 'A' then 1st condition else need to execute second condition @Barmar I want to eliminate syntax errors — mohan111, Mar 07 '23 at 17:18
You can fix syntax errors just by making sure that every `CASE` has a matching `END`. Whether it will do what you actually want is a different problem. — Barmar, Mar 07 '23 at 17:23
You can also use the simpler `IF(condition, truevalue, falsevalue)` expression, e.g. `IF(ROLE = ROLENUMBER, 'GOOD', 'BAD')` — Barmar, Mar 07 '23 at 17:24
and you can have multiple `WHEN` uses, but only one `ELSE` in a `CASE.....END`. — Luuk, Mar 07 '23 at 17:25

score 0 · Accepted Answer · answered Mar 07 '23 at 18:07

Does this capture all your "good" conditions?

  case 
     when section = 'A' and role = rolenumber then 'good'
     when section <> 'A' and role <> 0 and role is not null 
       and rolenumber <> 0  and rolenumber <> null and rolenumber <> role then 'good'
     when section <> 'A' and (role = 0 or role is null) and rolenumber = role then 'good'
     else 'bad'
  end

Kevin Jamali · Answer 2 · 2023-03-08T05:52:40.683

0

You can write it more simpler like this.

SELECT 
    CASE
        WHEN SECTION = 'A' AND ROLE = 'ROLENUMBER' THEN 'GOOD'
        WHEN
            SECTION != 'A' AND ROLE  0
                AND ROLE IS NOT NULL
                AND ROLENUMBER  0
                AND ROLENUMBER  NULL
                AND ROLENUMBER  ROLE
        THEN
            'GOOD'
        WHEN
            SECTION != 'A' AND ROLE = 0
                OR ROLE IS NULL AND ROLENUMBER = ROLE
        THEN
            'GOOD'
        ELSE 'BAD'
    END
FROM
    CLASSROOM

edited Mar 08 '23 at 05:52

answered Mar 08 '23 at 05:51

Kevin Jamali

1
2

No, because when will this be true? `ROLE IS NULL AND ROLENUMBER = ROLE` ==> Never, because when ROLE IS NULL it will never be equal to ROLENUMBER (and `AND` has a higher precedence than `OR` , see: [Mysql or/and precedence?](https://stackoverflow.com/questions/12345569/mysql-or-and-precedence)) – Luuk Mar 08 '23 at 18:21

Issue with Case Condition in My SQL Query

2 Answers2