September 3, 2004 at 12:59 am
Hi All,
Do you know how to use "CASE" in "WHERE" clause? Could you please give me some examples! Thanks in advance!
I need to write a query like this
SELECT table1.field1, table1.field1, table2.field2
FROM table1 , table2
WHERE table1.field1 *= table2.field1
AND table2.field2 = 2004 (this condition only when table2.field2 is not null )
(this is just a small part of a complicated query inside a complicated store procedure)
Regards!
Learning
September 3, 2004 at 4:05 am
Hi,
Please check the sql statement given below.
SELECT table1.field1, table1.field1, table2.field2
FROM table1 , table2
WHERE table1.field1 *= table2.field1
AND table2.field2 = (case when table2.field2 is null then null else 2004 end)
Hope this helps.
Nivedita Sundar.N
September 3, 2004 at 5:21 am
Not very complicated but hopefully will help you:
SELECT *
FROM pubs.dbo.authors AU
LEFT JOIN pubs.dbo.titleauthor TA
ON AU.au_id = CASE WHEN ISNULL(TA.au_id, '') = '' THEN NULL ELSE TA.au_id END
LEFT JOIN pubs.dbo.titles TT
ON TA.title_id = CASE WHEN ISNULL(TA.au_id, '') = '' THEN NULL
WHEN ISNULL(TA.au_id, '') <> '' THEN TA.title_id END
Good Hunting!
AJ Ahrens
webmaster@kritter.net
September 5, 2004 at 7:11 am
Why not try this:
SELECT table1.field1, table1.field1, table2.field2
FROM table1 , table2
WHERE table1.field1 *= table2.field1
AND (CASE WHEN table2.field2 is null THEN (1=1) ELSE (table2.field2 = 2004)END)
so the CASE will return either TRUE (if f2 is null or TRUE/FALSE depending on f2 comparison with 2004...
HTH
Luigi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply