Solved

SQL CASE statement help

Posted on 2014-10-31
6
184 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 19

Author Comment

by:erikTsomik
Comment Utility
Who would I improve the query overall
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 334 total points
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now