?
Solved

SQL CASE statement help

Posted on 2014-10-31
6
Medium Priority
?
206 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 1336 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
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 1336 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 664 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

752 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