0

I am designing a report using ColdFusion and SQL. The reprot is having 3 section. Now all the 3 sections are using the same set of data but manipulate it differently to show different results. I have used CTE for this. I have written 3 quires for the 3 sections.

WITH allOrders AS(  
Main query[With a lot of Joins]
)
SELECT  [Manupulated_Resultset_1]
FROM    allOrders 

WITH allOrders AS(  
Main query[With a lot of Joins]
)
SELECT  [Manupulated_Resultset_2]
FROM    allOrders 

WITH allOrders AS(  
Main query[With a lot of Joins]
)
SELECT  [Manupulated_Resultset_3]
FROM    allOrders

So for the three sections it is hitting to the database 3 times where as for all the cases the main query remains the same only the child query changes. I know this can be done in a much better way. So I need a way to store the main query results once and then use that to get the different manipulated results.

I can not use query of query in ColdFusion as to get the manipulated result sets it involves a lot of operation that QOQ does not support.

So is there any way in SQL to this?

Deepak Kumar Padhy
  • 4,128
  • 6
  • 43
  • 79

2 Answers2

5

Create a stored procedure, use a temp table to store the result of main query and then return multiple resultsets to the client in one go:

create procedure proc_name
as
set nocount on
;with allOrders as (  
Main query[With a lot of Joins]
)
select *
into #allOrders
from allOrders;

SELECT  [Manupulated_Resultset_1]
FROM    #allOrders;
SELECT  [Manupulated_Resultset_2]
FROM    #allOrders;
SELECT  [Manupulated_Resultset_3]
FROM    #allOrders;

GO

http://www.adobe.com/devnet/coldfusion/articles/stored_procs.html

dean
  • 9,960
  • 2
  • 25
  • 26
  • Good answer, but is the temp table necessary? Couldn't you simply select from the cte? – Dan Bracuk Apr 21 '14 at 12:49
  • 1
    @DanBracuk Well, CTE is statement scoped, so you can't have three individual select statements using the same CTE. If you somehow manage to pack it into one (using union all, for example), the temp table solution will still outperform the CTE one, for two reasons: first, materializing the intermediate result into temp table will provide accurate cardinalities to the optimizer, and second, the code inside the CTE will get executed every time you mention the CTE in the queries, as opposed to the temp table approach, where it gets executed only once. – dean Apr 21 '14 at 13:14
0

use a query of queries or create a DB view that joins the necessary tables so you can distribute some of the resources used to db side. best practice is the stored procedure

user1843529
  • 3
  • 1
  • 5