CTE Syntax 1

[briangriffin]

I have a query that used three cte’s, similar to:

Declare 
@v_startdate smalldatetime,
@v_enddate smalldatetime

with cte_one as

(Select some stuff where date between @v_startdate and @v_enddate),

Cte_two as

(Select different stuff where date between @v_startdate and @v_enddate),

Cte_three as

(select more stuff where date between @v_startdate and @v_enddate)


Select a.*, b.*, c.*

From 
cte_one a inner join cte_two
	On a.field = b.field

Inner join cte_three c
	On b.field = c.field

This works, but there is a problem with cte_one’s grouping. For some reason when I remove the only grouping field the whole query chokes, even though the components run very quickly.

I’m tired of fighting that, so I figured I’d just dump cte_one into a variable table instead:

Declare 
@v_startdate smalldatetime,
@v_enddate smalldatetime

Declare @v_cte_one_table table
	(fieldone int,
	Fieldtwo int)

Insert into @v_cte_one_table
	Select fieldone,
	Sum(fieldtwo)
	
	From sometable
	
	Group by fieldone

with Cte_two as

(Select different stuff where date between @v_startdate and @v_enddate),

Cte_three as

(select more stuff where date between @v_startdate and @v_enddate)


Select a.*, b.*, c.*

From 
cte_one_table a inner join cte_two
	On a.field = b.field

Inner join cte_three c
	On b.field = c.field

I know I have to have the main select immediately after the cte’s, but when I run this it considers the “with cte_two as” to be an continuation of the group by clause from populating the variable table. If I add ‘go’ afterwards then my date variables go out of scope.

This is a hacky approach, but I’ve spent too much time already on a fairly insignificant project. What’s the quickest way to get this to execute properly?

Thanks in advance…

 

[sparkbyte]

Try This

Declare 
@v_startdate smalldatetime,
@v_enddate smalldatetime

Declare @v_cte_one_table table
    (fieldone int,
    Fieldtwo int)

Insert into @v_cte_one_table
    Select fieldone,
    Sum(fieldtwo)
    
    From sometable
    
    Group by fieldone

;with Cte_two as

(Select different stuff where date between @v_startdate and @v_enddate),

Cte_three as

(select more stuff where date between @v_startdate and @v_enddate)


Select a.*, b.*, c.*

From 
cte_one_table a inner join cte_two
    On a.field = b.field

Inner join cte_three c
    On b.field = c.field

Thanks

John Fuhrman

 

[briangriffin]

Yes, thank you. Sometimes the simple things become the hardest.