Question 374525 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – IN Clause working opposite inside CASE Statement in sql

Having customer2 table
enter image description here

trying to execute below command

select AccountNO,id_Proof,
       CASE id_Proof
        when id_Proof in('Passport','PAN') then 'P'
        else id_Proof
       end as abc  
from customer2;

It working opposite

enter image description here

please suggest why IN clause getting opposite result inside case statement

Tags:

2

Answers


  1. 0

    You use CASE operator in the next syntax variation:

    CASE value WHEN compare_value THEN result [WHEN compare_value THEN result …] [ELSE result] END

    So in when id_Proof in('Passport','PAN') then 'P' the expression id_Proof in('Passport','PAN') is treated as compare_value.

    Depends on current id_Proof it produces 1, 0 or NULL.

    Then id_Proof is compared with this 1, 0 or NULL.

    If the value is 1, then id_Proof is ‘Passport’ or ‘PAN’, and the comparing produces FALSE, the result in this branch is not used. If the value is 0, then string-type value of id_Proof is converted to numeric datatype which produces 0 if the value have NOT a numeric prefix, and the comparing produces TRUE, the result in this branch is used (if a value starts from a digit then the convertion poroduces non-zero value and the comparing produces FALSE). If the value is NULL then any column value produces FALSE.

    Finally almost any value produces a result which looks like revertion. You may test this using the row where id_Proof value is, for example, '0 Passport' and '1 Passport'..

    You must use 2nd form of CASE operator: CASE WHEN id_Proof IN ('Passport','PAN') ...

    Login or Signup to reply.
  2. 0

    read the answer from @Akina or modify your case so:

    select AccountNO,id_Proof,
   CASE TRUE
    when name IN('Passport','PAN') then 'P'
    else name
   end as abc 
from customer2;

    the result of name IN(‘Passport’,’PAN’) is TRUE(1) OR FALSE(0) and it compares with the value from CASE TRUE

    sample

    select name in('Passport','PAN') as result, name
,       CASE TRUE
        when name IN('Passport','PAN') then 'P'
        else name
       end as abc  
from test_table LIMIT 5;

1   Passport.       P
0   Lorna Duran     Lorna Duran
1   PAN.            P
0   Allen Gibbons   Allen Gibbons
0   Melisa Porter   Melisa Porter
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search