Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Nested CASE WHEN in SELECT Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 2:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:49 AM
Points: 34, Visits: 132
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

Post #1522498
Posted Thursday, December 12, 2013 3:04 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 11:38 AM
Points: 896, Visits: 7,072
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
Post #1522501
Posted Thursday, December 12, 2013 3:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1522504
Posted Thursday, December 12, 2013 8:20 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:49 AM
Points: 34, Visits: 132
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
END
FROM @Employee E
INNER JOIN @Sales S ON
s.ID = e.ID
Post #1522555
Posted Friday, December 13, 2013 6:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 7,107, Visits: 13,462
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1522645
Posted Friday, December 13, 2013 7:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1522687
Posted Friday, December 13, 2013 8:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 7,107, Visits: 13,462
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1522715
Posted Friday, December 13, 2013 8:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 13,061, Visits: 11,889
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)


  Post Attachments 
dueling_banjos.jpg (123 views, 14.61 KB)
Post #1522736
Posted Friday, December 13, 2013 7:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:05 PM
Points: 36,710, Visits: 31,158
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1522921
Posted Monday, December 16, 2013 2:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:52 AM
Points: 7,107, Visits: 13,462
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1523122
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse