Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

case in where clasue

Posted on 2014-10-13
4
Medium Priority
?
125 Views
Last Modified: 2014-10-16
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
0
Comment
Question by:erikTsomik
4 Comments
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 40378079
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
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40378186
Thanks but it did not work, Now I get only with promotype =0 and none for promoType=1
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40378532
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.
0
 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40379268
If this logic you have provided is correct:

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

Then the conventional where conditions provided by Scott will match that logic:

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

I cannot see any point to using a  case expression in the where clause for such a simple need.

+edit,
unless you have an expectation about NULLs in PROMOTYPE that isn't met by this logic.

It would be useful to provide data instead of just saying it doesn't work.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question