Use DECODE for IF/ELSE Selection

Programmers often need a way to count and/or add up variable conditions for a group of rows. The DECODE statement provides a very efficient way of doing this. Because DECODE is rather complex, few programmers take the time to learn to use this statement to full advantage. The following statement uses DECODE to count the number of first, second, and third placings a racehorse has run:

SELECT

horse_name, to_char(sum(decode(position,1,1,0))),
to_char(sum(decode(position,2,1,0))), to_char(sum(decode(position,3,1,0)))
FROM winners
GROUP BY horse_name

In the sum(decode(position,2,1,0)) construct, we are saying that if the horse’s finishing position is 2 (second), add one to the count of seconds. The results of this statement appear as follows:
Horse
Firsts
Seconds
Thirds
Wild Charm
1
2
2
The alternative statement without the decode involves scanning the table three times, rather than once, as in the previous statement.

SELECT horse_name
, count(w1.position)
, count(w2.position)
, count(w3.position)
FROM winners w1, winners w2, winners w3
WHERE w1.horse_name = w2.horse_name
AND w2.horse_name = w3.horse_name
AND w1.position = 1
AND w2.position = 2
AND w3.position = 3
GROUP BY horse_name;

YHAWUFX3V4PW

Written by admin on January 27th, 2011 with comments disabled.
Read more articles on Oracle Tuning.

Related articles

Comments disabled

Comments on this article have been disabled.