Solved

SQL CASE statement help

Posted on 2014-10-31
6
199 Views
Last Modified: 2014-10-31
what is wrong with this CASe statement
and CASE WHEN pc.GenEmail  is NOT NULL
THEN 
	 pc.GenEmail  = (select U.email from users U where u.userKey=32164)
ELSE 
	pc.GenEmail is NULL
END as Value

Open in new window

0
Comment
Question by:erikTsomik
  • 3
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40416201
1 - afaik can't do a subquery in a CASE block
2 - don't need the ELSE block, as if NOT NULL then it's already NULL.
3 - Column to be assigned a value between CASE and the first WHEN, as you can't assign different columns based on different WHEN..THEN blocks.

Declare @email varchar(100) 
select @email = email from users where userKey=32164

...
CASE pc.GenEmail WHEN NOT NULL THEN @email END

Open in new window

btw I have an article out there called SQL Server CASE Solutions that is an image and demo-heavy tutorial on CASE blocks.
0
 
LVL 19

Author Comment

by:erikTsomik
ID: 40416223
that is great.. How would I improve this query to run faster
 Select distinct pc.codeID,pc.codeName,pc.AwardAmmount,pc.description  
                        from PromoCode PC
                     inner join lkupPromoCode_location PL  WITH (NOLOCK) on pc.CodeID = PL.codeID
                     left outer join lkuppromoCode_product PP WITH (NOLOCK) on pp.codeID = PC.CodeID
                 left outer join lkupPromocode_package PPP WITH (NOLOCK) on PPP.codeID = PC.CodeID
                
                     WHERE PC.status = 1  
                     and PC.StartDate <='#DateFormat(now(),"mm/dd/yyyy")#'  
                 and PC.promoType = 0
                 AND ((pc.genEmail  is NOT NULL and pc.genEmail = (select U.email from users U where u.userKey=#session.userid#)) OR   pc.genEmail  is NULL)

                 and DATEDIFF(D,pc.CodeIssueExpiry,GETDATE()) <= 0
                 and PL.LocationID  IN (Select lkupPromoCode_location.locationID from lkupPromoCode_location  where lkupPromoCode_location.codeID=pc.codeID and lkupPromoCode_location.type = 1)
                     and (ppp.packageID in
                                 (Select lkupPromoCode_package.packageID
                                 from lkupPromoCode_package  
                                 where lkupPromoCode_package.codeID=pc.codeID
                                 and lkupPromoCode_package.packageID in (#ArrayToList(ListToArray(session.promocode.packageKey))#)
                              and lkupPromoCode_package.type = 1)
                              OR pp.productID in (
                              Select lkuppromoCode_product.productID
                              from lkuppromoCode_product  
                              where lkuppromoCode_product.codeID=pc.codeID
                              and lkuppromoCode_product.productID in (#ArrayToList(ListToArray(session.promocode.productKey))#) and lkuppromoCode_product.type = 1 ))
0
 
LVL 65

Accepted Solution

by:
Jim Horn earned 334 total points
ID: 40416226
btw, where do you have the above statement is in the WHERE clause, try his instead...

WHERE (pc.GenEmail  = (select U.email from users U where u.userKey=32164))

or

Declare @email varchar(100)
@email = select U.email from users U where u.userKey=32164

...
WHERE ( pc.GenEmail = @email )

Open in new window

0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 19

Author Comment

by:erikTsomik
ID: 40416267
Who would I improve the query overall
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 334 total points
ID: 40416292
Define 'improve the query overall', as that's a pretty open-ended comment, and since experts here do not have access to the source of data our recommending solutions is limited.

Offhand..
1 - Replace the INs with JOINs, especially if the IN subquery returns a lot of rows.
2 - Use table aliases to make the query easier to read.
3 - Use proper indenting such as {tab} or three spaces to make the query easier to read.
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
ID: 40416784
no points please

In addition to Jim's 3 points

4. the most obvious destroyer of performance in your query is the second word

5. You would probably find using EXISTS an easier way to replace those IN() condition

------------------------
BUT:
You should open another question for "How would I improve this query overall"
but before you do that generate the execution plan and upload the .sqlplan file with your new question.

see: How to Create an Explain Plan
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

763 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