March 4, 2014 at 6:09 am
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
March 4, 2014 at 6:22 am
You've missed the FROM list in CTE3.
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 4, 2014 at 6:35 am
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.)
March 4, 2014 at 6:51 am
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
March 4, 2014 at 7:02 am
;
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.)
March 4, 2014 at 10:10 am
ms-techie (3/4/2014)
Actually from CTE is like this .. All 3 are from SAME TABLE , with different conditions in each caseWith 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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply