Multiple CTE structure to be changed to a single CTE

Question

Multiple CTE structure to be changed to a single CTE

VSSGeorge

SSCrazy Eights

Points: 8151
More actions
October 8, 2014 at 5:04 am

#291453

Hi
I have a requirement where my SQL query need to be changed such that, I have two CTEs used in my query, where the first CTE is defined and inside the second CTE, the first CTE name is referred in the join condition. I need to change the multiple CTE structure to single CTE. Is it possible?
The present code structure is this:
;WITH CTE1 AS
(
SELECT [Testcode] = 'A7158', [Parametername] = '2,3,7,8-TetraCDD', [Parametercode] = '7300A101', [Parameter_neu] = '2,3,7,8-TCDD', [Relevant] = 'ja', [Parameterart] = 'Analysenwert', [Prinzip] = 'GC-HRMS'
UNION
SELECT 'A7158', '1,2,3,7,8-PentaCDD', '7300A102', '1,2,3,7,8-PCDD', 'ja', 'Analysenwert', 'GC-HRMS'
UNION
SELECT 'A7158', '1,2,3,4,7,8-HexaCDD', '7300A103', '1,2,3,4,7,8-HxCDD', 'ja', 'Analysenwert', 'GC-HRMS'
),
CTE2
AS
SELECT Distinct
ISNULL(CONVERT(nvarchar(1000), s.clientSampleCode), '') + ' .....
FROM UsersClientsAndContracts ucc
JOIN ClientSamples cs
ON cs.ClientId = ucc.clientId
LEFT JOIN CTE1 LESI WITH (NOLOCK)
ON LESI.Parametercode_COMLIMS = cs.parameterCode COLLATE Latin1_General_CI_AS

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

Why do you need to change it to a single CTE? Cascading CTEs exist so you can accomplish things you can't do with one query. It's used to "divide and conquer" the problem you have to solve. Think of a CTE as a view of the data, nothing more. If the query performs well and this requirement exists because someone else doesn't understand it, then maybe it's an issue of educating them instead of rewriting the query.

Honestly, I'd be looking at that NOLOCK instead of the CTE levels.

http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Sure, you can use the first CTE as a subquery in the second CTE, resulting in only one CTE.

But semantically nothing has changed, since CTEs are subqueries anyway. The execution plans will be the same.

ps: the semicolon is a statement terminator

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

VSSGeorge SSCrazy Eights Points: 8151 More actions · Answer 3

Thanks Koen for that input. Can you elaborate on that point.

How my query can be changed to accommodate this.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Junglee_George (10/8/2014)
Thanks Koen for that input. Can you elaborate on that point.
How my query can be changed to accommodate this.

Which point? The two CTEs to one, or the terminator?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

Ed Wagner (10/8/2014)
Why do you need to change it to a single CTE? Cascading CTEs exist so you can accomplish things you can't do with one query. It's used to "divide and conquer" the problem you have to solve. Think of a CTE as a view of the data, nothing more. If the query performs well and this requirement exists because someone else doesn't understand it, then maybe it's an issue of educating them instead of rewriting the query.
Honestly, I'd be looking at that NOLOCK instead of the CTE levels.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

The funny thing is the only use of that dreaded hint is on the CTE. 😉

_______________________________________________________________

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/

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

Junglee_George (10/8/2014)
Thanks Koen for that input. Can you elaborate on that point.
How my query can be changed to accommodate this.

Seems like wasted effort to me. You already have this in a cte so you are not going to gain anything (except code that is harder to read) by moving the first cte as a subquery of the first.

WITH CTE2 AS

SELECT Distinct

ISNULL(CONVERT(nvarchar(1000), s.clientSampleCode), '')

FROM UsersClientsAndContracts ucc

JOIN ClientSamples cs

ON cs.ClientId = ucc.clientId

LEFT JOIN

(

SELECT [Testcode] = 'A7158', [Parametername] = '2,3,7,8-TetraCDD', [Parametercode] = '7300A101', [Parameter_neu] = '2,3,7,8-TCDD', [Relevant] = 'ja', [Parameterart] = 'Analysenwert', [Prinzip] = 'GC-HRMS'

UNION

SELECT 'A7158', '1,2,3,7,8-PentaCDD', '7300A102', '1,2,3,7,8-PCDD', 'ja', 'Analysenwert', 'GC-HRMS'