March 9, 2011 at 4:16 am
Hi All,
I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.
I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).
Any ideas???
March 9, 2011 at 4:35 am
clarmatt73 (3/9/2011)
Hi All,I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.
I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).
Any ideas???
You could set up each UNIONed query (or all three middle bits) as a CTE. Nothing stopping you from referencing a CTE twice in the same query.
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
March 9, 2011 at 5:11 am
Hi Chris,
Thats great, thanks for the help.
Matt
March 9, 2011 at 5:31 am
You're welcome Matt. If you're still unsure, post up the whole query.
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
March 9, 2011 at 5:56 am
Chris Morris-439714 (3/9/2011)
clarmatt73 (3/9/2011)
Hi All,I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.
I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).
Any ideas???
You could set up each UNIONed query (or all three middle bits) as a CTE. Nothing stopping you from referencing a CTE twice in the same query.
Oh... be careful, Chris. It really does depend, in this case. When you reference a CTE twice in the same query, the CTE get's executed twice just as if it were a view. If it's a complicated CTE (or string of CTE's), you're doubling the amount of time and resources the query will take to execute.
My recommendation on something like this is to build a report staging table and populate it using "Divide'n'Conquer" methods. That way you can do easy and fast things like using @@ROWCOUNT to get the count from the query to be included in the footer.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 9, 2011 at 6:11 am
Jeff Moden (3/9/2011)
Chris Morris-439714 (3/9/2011)
clarmatt73 (3/9/2011)
Hi All,I have a stored procedure that has five union queries within it, the first and last queries in the code are the Header and Footer for the export i have created.
I need to get the total volume of rows from the three middle queries to appear in the final query (the footer).
Any ideas???
You could set up each UNIONed query (or all three middle bits) as a CTE. Nothing stopping you from referencing a CTE twice in the same query.
Oh... be careful, Chris. It really does depend, in this case. When you reference a CTE twice in the same query, the CTE get's executed twice just as if it were a view. If it's a complicated CTE (or string of CTE's), you're doubling the amount of time and resources the query will take to execute.
My recommendation on something like this is to build a report staging table and populate it using "Divide'n'Conquer" methods. That way you can do easy and fast things like using @@ROWCOUNT to get the count from the query to be included in the footer.
Good point, Jeff. It's easy to perceive a CTE as a derived table when they're quite different as you point out. It depends mostly upon two things - the rowcounts and the complexity of the CTE's.
Here's an alternative approach which I use from time to time:
SELECT CAST('1 Header' AS VARCHAR(20)) AS Section, ...
INTO #LocalTemporaryTable
UNION ALL
SELECT CAST('2 Middle 1' AS VARCHAR(20)), ...
UNION ALL
SELECT CAST('3 Middle 2' AS VARCHAR(20)), ...
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
March 11, 2011 at 6:05 am
The alternative is good but it's still ok to use the CTE... I just dump it's results into a TempTable if I need to call it more than once so it doesn't have to run more than once.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply