Use CASE Expressions Rather than Multiple Queries


Use CASE Expressions Rather than Multiple  Queries

Use CASE expressions rather than multiple queries when you need to perform many calculations on the same rows in a table. The following example uses multiple queries to count the number of products within various price ranges (bad):

 -- BAD (three separate queries when one CASE statement would work)  SELECT COUNT(*)   FROM products   WHERE price  <  13;  COUNT(*) ----------  2  SELECT COUNT(*)   FROM products   WHERE price BETWEEN 13 AND 15;    COUNT(*) ----------  5  SELECT COUNT(*)   FROM products   WHERE price  >  15;  COUNT(*) ----------  5 

Rather than using the three queries just shown, you should write one query that uses CAS E expressions. For example:

 -- GOOD (one query with a CASE expression rather than three queries)  SELECT   COUNT(CASE WHEN price  <  13 THEN 1 ELSE null END) low,   COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med,   COUNT(CASE WHEN price  >  15 THEN 1 ELSE null END) high   FROM products;  LOW MED HIGH ---------- ---------- ----------  2 5 5 

Notice the counts of the products with prices below $13 are labeled as low , products between $13 and $15 are labeled med , and products greater than $15 are labeled high .

Note  

You can, of course, use overlapping ranges and different functions in your CASE expressions.




Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net