How to Perform Multiple Comparisons in Oracle SQL Query Using Select .. Case .. When .. Else


Oracle 10g R1 onwards the case .. when clause has been added to the select statement which can perform several comparisons in a single query. Prior to that we should have to use the DECODE function to achieve this goal.


In below example I am doing the classification of birds and animals. You can group and compare a set of items at a time.   
SELECT CASE
  WHEN 'COW' IN
    ('COW','CAT','BULL','HORSE','ELEPHANT')
    THEN 'ANIMALS'
  WHEN 'COW' IN
    ('CROW', 'PEACOAK','HEN','PARROT') in
    THEN 'BIRDS'
  ELSE 'unknown day' END
FROM DUAL;

In below example instead of &input you can use a column from a table for the comparison.

SQL> SELECT CASE
  WHEN '&input' IN
    ('COW','CAT','BULL','HORSE','ELEPHANT')
    THEN 'ANIMALS'
  WHEN '&input' IN
    ('CROW', 'PEACOAK','HEN','PARROT')
    THEN 'BIRDS'
  ELSE '&input' END
FROM DUAL;  2    3    4    5    6    7    8    9
Enter value for input: COW
old   2:   WHEN '&input' IN
new   2:   WHEN 'COW' IN
Enter value for input: COW
old   5:   WHEN '&input' IN
new   5:   WHEN 'COW' IN
Enter value for input: COW
old   8:   ELSE '&input' END
new   8:   ELSE 'COW' END

CASEWHE
-------
ANIMALS

SQL> /
Enter value for input: CROW
old   2:   WHEN '&input' IN
new   2:   WHEN 'CROW' IN
Enter value for input: CROW
old   5:   WHEN '&input' IN
new   5:   WHEN 'CROW' IN
Enter value for input: CROW
old   8:   ELSE '&input' END
new   8:   ELSE 'CROW' END

CASEWHE
-------
BIRDS
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google