'Like' withing CASE statement ???

[santoshsri]

I wish to write a like statement within CASE statement .. Can it be possible , because I am getting error whenever I am using Like in CASE statement.The SQL is something like this
Select
CASE CBPQTN_BOOK_STA T
WHEN '22' THEN 'Declined'
WHEN '21' THEN 'Withdrawn'
WHEN '00' THEN 'Quoted'
WHEN '23' THEN 'Quoted'
WHEN '24' THEN 'Quoted'
WHEN '27' THEN 'Quoted'
WHEN 'Y0' THEN 'Quoted'
WHEN '0 ' THEN 'Quoted'
WHEN 'X ' THEN 'Quoted'
WHEN 'EY' THEN 'Booked'
WHEN 'E0' THEN 'Booked'
WHEN 'Z0' THEN 'Booked'
WHEN LIKE '%Z' THEN 'Issued'
WHEN LIKE '%X' THEN 'Issued'
WHEN 'X0' THEN 'Issued'
WHEN 'PY' THEN 'Issued'
WHEN 'ZY' THEN 'Issued'
END AS STATUS_REASON
From ..



Guys, Please help me in rectifying this ..

Thanks in Advance ...

[dorinbogdan]

SELECT CASE CBPQTN_BOOK_STAT
WHEN '22' THEN 'Declined'
WHEN '21' THEN 'Withdrawn'
WHEN '00' THEN 'Quoted'
WHEN '23' THEN 'Quoted'
WHEN '24' THEN 'Quoted'
WHEN '27' THEN 'Quoted'
WHEN 'Y0' THEN 'Quoted'
WHEN '0 ' THEN 'Quoted'
WHEN 'X ' THEN 'Quoted'
WHEN 'EY' THEN 'Booked'
WHEN 'E0' THEN 'Booked'
WHEN 'Z0' THEN 'Booked'
WHEN 'X0' THEN 'Issued'
WHEN 'PY' THEN 'Issued'
WHEN 'ZY' THEN 'Issued'
ELSE 
 (CASE 
  WHEN CBPQTN_BOOK_STAT LIKE '%Z' 
  THEN 'Issued' 
  ELSE 
   (CASE WHEN CBPQTN_BOOK_STAT LIKE '%X' THEN 'Issued' 
    END) 
  END)
END AS STATUS_REASON

[santoshsri]

SELECT CASE CBPQTN_BOOK_STAT
WHEN '22' THEN 'Declined'
WHEN '21' THEN 'Withdrawn'
WHEN '00' THEN 'Quoted'
WHEN '23' THEN 'Quoted'
WHEN '24' THEN 'Quoted'
WHEN '27' THEN 'Quoted'
WHEN 'Y0' THEN 'Quoted'
WHEN '0 ' THEN 'Quoted'
WHEN 'X ' THEN 'Quoted'
WHEN 'EY' THEN 'Booked'
WHEN 'E0' THEN 'Booked'
WHEN 'Z0' THEN 'Booked'
WHEN 'X0' THEN 'Issued'
WHEN 'PY' THEN 'Issued'
WHEN 'ZY' THEN 'Issued'
ELSE 
 (CASE 
  WHEN CBPQTN_BOOK_STAT LIKE '%Z' 
  THEN 'Issued' 
  ELSE 
   (CASE WHEN CBPQTN_BOOK_STAT LIKE '%X' THEN 'Issued' 
    END) 
  END)
END AS STATUS_REASON

Thanks so much .. it solved my purpose !

[dorinbogdan]

You're welcome.
God bless you,
Dorin.

[vishuraj2000]

Hi, Dorin

Even I have to thank you.

As the (like in case) statement helped me a lot.


Thanks and regards,

vishu

[Fede]

Thank you!!!
This post helped me a lot!!!

[NeoPa]

Nice work Dorin. An alternative (just for lines #18 through #24) would be to use OR in the WHEN construct :

 (CASE 
    WHEN (CBPQTN_BOOK_STAT LIKE '%X')
      OR (CBPQTN_BOOK_STAT LIKE '%Z') THEN 'Issued'
  END)

[ck9663]

Or convert everything to the Searched CASE Expression instead and group all those expression that will return same result using the OR operator.

Here's the full syntax of CASE expression. Use the second one...

Happy Coding!!!

~~ CK