asked on

case in where clasue

I am trying to write a query where I think I can use CASe statement . So what I want to do is say if the value is 0 look in one table other wise look in another table.

Some thing like this

CASE WHEN PROMOTYPE = 0
                   THEN
                        PT.newCode = 'XXX'
                   ELSE
                        PC.codeNAme = 'XCXX'
                   END

Scott Pletcher

WHERE
( (PROMOTYPE = 0 AND PT.newCode = 'XXX') OR
(PROMOTYPE <> 0 AND PC.codeNAme = 'XCXX') )

Or, to use a CASE:

WHERE 1 =
CASE WHEN PROMOTYPE = 0 THEN CASE WHEN PT.newCode = 'XXX' THEN 1 ELSE 0 END
ELSE CASE WHEN PC.codeNAme = 'XCXX' THEN 1 ELSE 0 END
END

erikTsomik

ASKER

Thanks but it did not work, Now I get only with promotype =0 and none for promoType=1

Jim Horn

Assuming both PT and PC tables are referenced via FROM..JOIN, and PT.newCode and pc.CodeName have the same datatype..

SELECT blah, blah, blah, 
   CASE WHEN PROMOTYPE = 0 THEN PT.newCode 
   ELSE PC.codeNAme END as name_goes_here
FROM whatever

Open in new window

btw if it helps I have an article out there called SQL Server CASE Solutions that is an code-and-image-heavy tutorial on CASE blocks.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial