Nested CASE WHEN in SELECT

Question

Post reply

Nested CASE WHEN in SELECT

kk_tech

Default port

Points: 1492
More actions
December 12, 2013 at 2:59 pm

#280308

I am getting duplicate result when I nest CASE within SELECT statement. I just wanted to check column2 with many possibilities, I am getting duplicate(repeating same row) many time.
Am I doing something wrong here?, or How should Write new query.
SELECT
Column1,Column2,
CASE Column3
WHEN '123' THEN
CASE WHEN Column1 IS NULL THEN 'xyz'
WHEN Table2.Column1 IS NOT NULL THEN 'xyz'
-- More WHEN...
ELSE 'Unknown'
END
WHEN '456' THEN
CASE WHEN Column1 IS NULL THEN 'xyz'
WHEN Table2.Column1 <> 'ABC' THEN 'Any value'
Else 'Unknown'
-- More WHEN...
END
WHEN '789' THEN
CASE WHEN Column1 IS NULL THEN 'xyz'
WHEN Table2.Column1 IS NOT NULL THEN 'xyz'
Else 'Unknown'
-- More WHEN...
END
END
END AS "Problem"
FROM Table1
INNER JOIN Table2 ON
Table1.Column4 = Table2.Column4

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

David Webb-CDS SSCoach Points: 17398 More actions · Answer 1

If you remove all the case logic and just execute the query with the join intact, do you still get the duplicate rows?

And then again, I might be wrong ...
David Webb

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

kk_tech Default port Points: 1492 More actions · Answer 3

Thank you David and Sean, You both correct that my Query without CASE is returning duplicate due to JOIN. I get it now.

, However question is I have a large amount of rows(900K) in result query where actual Distinct rows are 500K, and I need to update 500K rows only based on CASE statement every time I run this query, let me give you sample SELECT example to get an idea. Do you think this is the way to go or any other way to perform beside CASE. They are actual table in the database not like temp table or variable. I will be only updating "Sale" column

Note: There will be more than 5 different INNER AND LEFT JOIN in future and about 40 different "ProductName"

Hope this help...

Declare @Employee Table

(ID int NOT NULL

,FirstName VARCHAR(100) NULL

,Area VARCHAR(100) NULL

)

Declare @Sales Table

(ID int NOT NULL

,Qty int NULL

,ProductName VARCHAR(100) NULL

)

INSERT INTO @Employee

(ID,FirstName,Area)

VALUES

(100,'Rick','California')

,(101,'R','Maryland')

,(102,'Mark','New York')

,(103,'Lisa','Chicago')

,(104,'Kate','Baltimore')

---

INSERT INTO @Sales

(ID,Qty,ProductName)

VALUES

(100,5,'Orange')

,(102,10,'Apple')

,(100,100,'Orange')

,(103,2,'Banana')

,(104,3,'Strawberry')

SELECT

e.ID,e.FirstName,e.Area

,s.ID,s.Qty,s.ProductName

,

"Sale" =

CASE ProductName

WHEN 'Orange' THEN

CASE WHEN Qty > 50 THEN 'Above target'

ElSE 'Below target'

END

WHEN 'Apple' THEN

CASE WHEN Qty < 50 THEN 'Need to sell more'

ELSE 'Above target'

END

FROM @Employee E

INNER JOIN @Sales S ON

s.ID = e.ID

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.

It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.

I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.
I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉

Heh I know that you know this and I know that you know that I know you know it too. A lot of folks don't though, and I'd hate to see you getting quoted. I think I can hear Duelling Banjo's now.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.
I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉
Heh I know that you know this and I know that you know that I know you know it too. A lot of folks don't though, and I'd hate to see you getting quoted. I think I can hear Duelling Banjo's now.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.
I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉
Heh I know that you know this and I know that you know that I know you know it too. A lot of folks don't though, and I'd hate to see you getting quoted. I think I can hear Duelling Banjo's now.

Unbelievable... that looks so much like the flux capacitor that I have installed on my latest physical version of DBCC TIMEWARP.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Jeff Moden (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.
I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉
Heh I know that you know this and I know that you know that I know you know it too. A lot of folks don't though, and I'd hate to see you getting quoted. I think I can hear Duelling Banjo's now.
Unbelievable... that looks so much like the flux capacitor that I have installed on my latest physical version of DBCC TIMEWARP.

So what are you using as fuel? I find cucumbers last longer than banana skins. Twice as long if I dip the end in copper-coloured paint.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ScottPletcher SSC Guru Points: 101098 More actions · Answer 10

I think you need a GROUP BY rather than a DISTINCT.

Is the code below closer/close to the final output you want?

SELECT

e.ID,e.FirstName,e.Area

,s.ID,s.Qty,s.ProductName, s.Sale

FROM (

SELECT ID, ProductName, SUM(Qty) AS Qty,

CASE ProductName

WHEN 'Apple' THEN CASE WHEN SUM(Qty) < 50 THEN 'Sell more' ELSE 'Above target' END

--WHEN 'Banana' THEN CASE ... END

WHEN 'Orange' THEN CASE WHEN SUM(Qty) > 50 THEN 'Above target' ELSE 'Below target' END

--WHEN 'Strawberry' THEN CASE ... END

END AS [Sale]

FROM @Sales

GROUP BY ID, ProductName

) AS s

INNER JOIN @Employee e ON

e.ID = s.ID

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 11

ChrisM@Work (12/16/2013)
Jeff Moden (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.
I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉
Heh I know that you know this and I know that you know that I know you know it too. A lot of folks don't though, and I'd hate to see you getting quoted. I think I can hear Duelling Banjo's now.
Unbelievable... that looks so much like the flux capacitor that I have installed on my latest physical version of DBCC TIMEWARP.
So what are you using as fuel? I find cucumbers last longer than banana skins. Twice as long if I dip the end in copper-coloured paint.

I believe I've found the most energetic fuel yet... ground up Developer's egos. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sean Lange SSC Guru Points: 286590 More actions · Answer 12

Jeff Moden (12/16/2013)
ChrisM@Work (12/16/2013)
Jeff Moden (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.
I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉
Heh I know that you know this and I know that you know that I know you know it too. A lot of folks don't though, and I'd hate to see you getting quoted. I think I can hear Duelling Banjo's now.
Unbelievable... that looks so much like the flux capacitor that I have installed on my latest physical version of DBCC TIMEWARP.
So what are you using as fuel? I find cucumbers last longer than banana skins. Twice as long if I dip the end in copper-coloured paint.
I believe I've found the most energetic fuel yet... ground up Developer's egos. 😀

Better be careful there. That much uncontained burst energy means that you are not going to be able to accurately define the settings for arrival. It will most certainly go somewhere FAST but you just can't control the direction. The release is far too explosive.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Sean Lange (12/16/2013)
Jeff Moden (12/16/2013)
ChrisM@Work (12/16/2013)
Jeff Moden (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single column in the rows that are returned. The "duplicates" have nothing to do with a case expression, it has to do with your query.
It won't make a difference with this query but CASE in a SELECT DISTINCT statement can affect the number of rows returned, if the result of the expression changes the state of the output row from not-dupe to dupe or vice versa.
I had a feeling somebody might bring that up. I suppose I should have added a caveat about it not affecting the number of rows unless there is aggregation or distinct. 😉
Heh I know that you know this and I know that you know that I know you know it too. A lot of folks don't though, and I'd hate to see you getting quoted. I think I can hear Duelling Banjo's now.
Unbelievable... that looks so much like the flux capacitor that I have installed on my latest physical version of DBCC TIMEWARP.
So what are you using as fuel? I find cucumbers last longer than banana skins. Twice as long if I dip the end in copper-coloured paint.
I believe I've found the most energetic fuel yet... ground up Developer's egos. 😀
Better be careful there. That much uncontained burst energy means that you are not going to be able to accurately define the settings for arrival. It will most certainly go somewhere FAST but you just can't control the direction. The release is far too explosive.

I was thinking the same. More appropriate for DBCC NEXTBIGBANG 😀

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden