Monday, February 26, 2007

CASE expression techniques

I'm sure that all of us know and do CASE expression in their projects and reports. I'd like to share a couple of methodes that I used rarely , but it looks useful

Using Case in JOIN query
CASE expression checks title's type column and assign an appropriate column to be referenced

SELECT a.au_lname, a.au_fname, a.address,
t.title, t.type
FROM authors a INNER JOIN
titleauthor ta ON ta.au_id = a.au_id INNER JOIN
titles t ON t.title_id = ta.title_id
INNER JOIN publishers p on t.pub_id =
CASE WHEN t.type = 'Business' THEN p.pub_id ELSE null END
INNER JOIN stores s on s.stor_id =
CASE WHEN t.type = 'Popular_comp' THEN t.title_id ELSE null END


---Or

SELECT FROM Territory AS t JOIN Customers AS c
ON
CASE WHEN t.countrycode = 'US' OR t.countrycode = 'CA' then t.Areacode ELSE t.countrycode END=
CASE WHEN c.countrycode = 'US' OR c.countrycode = 'CA' then c.Areacode ELSE c.countrycode END

The following CASE expression we use more 'frequently'
This CASE expression returns 1 or 0 (True ,False)
SELECT
columname,
CASE
WHEN EXISTS (
SELECT * FROM Table1 t1
WHERE t1.col1= 1 AND t1.col2= t2.col2)
THEN 1
ELSE 0
END AS Alias
FROM Table2 t2
ORDER BY columname

5 comments:

Sreenath said...

This is indeed a useful technique. I am trying to use similar approach in Oracle but is is throwing error as Invalid column at the join. Any suggestions would be helpful.

haiyan said...

The Requiem shows up so many cool pictures that got me thinking a lot. And I really mean that a lot of requiem gold is very important and required. First off if the name is not taken the name and some requiem lant of book Requiem I do not if it is allowed to name a book. Some requiem money will well enough chatting better go and write this book. Comment on this please should I write this book or not. See you. In Requiem, there is blood. Monsters, blood, bigger monsters, and yes, more blood and need cheap requiem lant. You know what that requiem online gold means; there are factions, races changing sides, and all out war.

products said...

China Wholesale has been described as the world’s factory. This phenomenom is typified by the rise ofbusiness. Incredible range of products available with China Wholesalers “Low Price and High Quality” not only reaches directly to their target clients worldwide but also ensures that wholesale from china from China means margins you cannot find elsewhere and buy products wholesaleChina Wholesale will skyroket your profits.wedding dressescheap naruto cosplayanime cosplay

products said...

Women’s nike tn Shox Rivalry est le modèle féminin le plus tendance de baskets pour le sport. tn chaussuresConcernant la semelle :spyder jacketsCheap Brand Jeans Shop - True Religion Jeans cheap nike shox & Puma Shoes Online- tn nike,Diesel Jeans le caoutchouc extérieur, l’EVA intermédiaire Levis Jeanset le textile intérieur s’associent pour attribuer à la.ed hardy shirts pretty fitCharlestoncheap columbia jackets. turned a pair of double plays to do the trick.Lacoste Polo Shirts, puma basket, Burberry Polo Shirts.wholesale Lacoste polo shirts and cheap polo shirtswith great price.Thank you so much!!cheap polo shirts men'ssweate,gillette mach3 razor bladesfor men.As for Cheap Evisu JeansCheap Armani Jeanspolo shirtsPuma shoes

aiya said...
This comment has been removed by a blog administrator.