Saturday, July 16, 2011

How to write multiple case in select statement with alias???

select username,expiry_date,
  (CASE
    WHEN expiry_date IS NOT NULL and expiry_date > sysdate then 'OPEN'
    WHEN expiry_date IS NOT NULL and expiry_date < sysdate then 'LOCKED'
    WHEN expiry_date IS NULL then account_status
  END) "account_st",account_status
from dba_users where username in ('ABC','XYZ');

No comments:

Post a Comment