Tuesday, April 11, 2006

SQL 99 - CASE

SQL 99 supports
CASE statement within SELECT

like

SELECT col_a, col_b
CASE col_c
WHEN col_c = 1 THEN 'ONE'
WHEN col_c = 2 THEN 'TWO'
ELSE 'OTHER'
END
FROM tbl;

Probably this does an outer join and a Union, not sure, but this is handy and much easier to use than JOINs.

If any of u know the internals of this , u'r welcome to describe it here.

--
Ani

1 comment:

Ratish said...

One More Usage of CASE:
-------------------------------
we can use the CASE statement
to create columns in a crosstab query when one knows the expected results beforehand

eg:
SELECT SUM(CASE WHEN month(some_date)between 1 and 3 THEN qty ELSE 0 END) AS firstQuater,
SUM(CASE WHEN month(some_date) between 4 and 6 THEN qty ELSE 0 END) AS SecondQuater ....