advertisements
_____________________________________________________________________________________________________________________
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