Solved

SQL CASE statement help

Posted on 2014-10-31
6
203 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 66

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 66

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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 19

Author Comment

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

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 49

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

726 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