how to use One CTE value in another CTE ,in SQL 2008 R2

Question

how to use One CTE value in another CTE ,in SQL 2008 R2

ms-techie

SSChasing Mays

Points: 627
More actions
March 4, 2014 at 6:09 am

#284185

I have 2 CTE . I want to use value from 1st and 2nd CTE in my 3rd CTE .
Is it possible ?
With CTE1 AS
(
Select count(Column1) as Cnt_Column1_Tbl1 FROM TABLE1
),
CTE2 AS
(
Select count(Column2) as Cnt_Column2_Tbl2 from TABLE2
)
,
CTE3 AS
(
Select ( CTE1.Cnt_Column1_Tbl1 - CTE2.Cnt_Column2_Tbl2) -- I get an error on this line here
)
The error I get is
The multi-part identifier "CTE1.Cnt_Column1_Tbl1" could not be bound.
Please help

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

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

You've missed the FROM list in CTE3.

^{“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

David McKinney SSChampion Points: 10472 More actions · Answer 2

The first point is that at some stage you want to SELECT (outside of the CTE) to get some results.

....CTE3 AS

(

Select ( CTE1.Cnt_Column1_Tbl1 - CTE2.Cnt_Column2_Tbl2) -- I get an error on this line here

)

select * FROM CTE3 -- without the select you'll get syntax errors.

The second point is that in CTE3, when you're referring to CTE1 and CTE2 you should do so as if they were tables i.e. your select statement should have a FROM element to it, as well as some sort of JOIN. (You may have to add more fields to CTE1 and CTE2 so that you've got the appropriate fields to join on.)

ms-techie SSChasing Mays Points: 627 More actions · Answer 3

Actually from CTE is like this .. All 3 are from SAME TABLE , with different conditions in each case

With CTE1 AS

(

Select count(Column1) as Cnt_Column1_Tbl1 FROM TABLE1 with some CONDITION1

),

CTE2 AS

(

Select count(Column2) as Cnt_Column2_Tbl1 from TABLE1 with some conditions2

)

,

CTE3 AS

(

Select ( CTE1.Cnt_Column1_Tbl1 - CTE2.Cnt_Column2_Tbl1) from TABLE1-- I get an error on this line here

)

The error I get is

The multi-part identifier "CTE1.Cnt_Column1_Tbl1" could not be bound.

Please help

David McKinney SSChampion Points: 10472 More actions · Answer 4

;

With CTE1 AS

(

Select count(Column1) as Cnt_Column1_Tbl1 FROM TABLE1 with some CONDITION1

),

CTE2 AS

(

Select count(Column2) as Cnt_Column2_Tbl1 from TABLE1 with some conditions2

)

Select ( CTE1.Cnt_Column1_Tbl1 - CTE2.Cnt_Column2_Tbl1) FROM CTE1, CTE2

given that apparently CTE1 and CTE2 each return just one row, you could include them in the FROM without a join (or effectively a CROSS JOIN)

If you also need fields from TABLE1, you can refer also to it in the FROM clause.

But the point is that you still have to refer to them in CT3 (or in the last select as I've done above.)

sqlslacker SSChasing Mays Points: 657 More actions · Answer 5

ms-techie (3/4/2014)
Actually from CTE is like this .. All 3 are from SAME TABLE , with different conditions in each case
With CTE1 AS
(
Select count(Column1) as Cnt_Column1_Tbl1 FROM TABLE1 with some CONDITION1
),
CTE2 AS
(
Select count(Column2) as Cnt_Column2_Tbl1 from TABLE1 with some conditions2
)
,
CTE3 AS
(
Select ( CTE1.Cnt_Column1_Tbl1 - CTE2.Cnt_Column2_Tbl1) from TABLE1-- I get an error on this line here
)
The error I get is
The multi-part identifier "CTE1.Cnt_Column1_Tbl1" could not be bound.
Please help

Okay, so here's (hopefully) a little help for your confusion.

The input of CTE1 is whatever you select FROM within the query. The output is that input set modified by any filters (WHERE clause, expressions like COUNT(), that kind of thing.)

The input of CTE2 is whatever you select FROM within the query, with the output of CTE1 being AVAILABLE but not NECESSARY. As far as CTE2 is concerned, CTE1 is just the same as any other table in the database at this point. It doesn't yet know about CTE3, but it does know about CTE1. That should be clear from how CTE2 is defined in your example, since it references TABLE1 but not CTE1. The output of CTE2 is the input set of CTE2 modified by any filters (just like CTE1!)

So, now we get to CTE3. The input of CTE3 is (guess what?) whatever you select FROM within the query. You can't select, for example, TABLE1.Column1 without some sort of FROM clause, and CTE1 and CTE2 are just the same way - they're just tables, as far as CTE3 is concerned.