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

_____________________________________________________________________________________________________________________

0 comments:

Post a Comment

 

acehints.com Copyright 2011-17 All Rights Reserved | Site Map | Contact | Disclaimer | Google