Solved

SQL CASE statement help

Posted on 2014-10-31
6
201 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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!

 
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

Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

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. …
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

737 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