Multiple CTE in One SELECT Statement Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Multiple CTE in One SELECT Statement Query

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-05-08 : 10:59:19

Hello folks,

I am trying to apply the logic from the following resource:

http://blog.sqlauthority.com/2009/08/08/sql-server-multiple-cte-in-one-select-statement-query/

but cannot get it to work with my logic for some reason.

For example, the following query:

;WITH CTE1 AS (SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Col1),
CTE2 AS (SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

Produces the following output:

Col1 | Col2
2014-05-08 10:55:54 | 2014/05/08

But, as soon as I try to do something else like:

;WITH CTE1 AS (SELECT COUNT(login) FROM userinfo AS Col1),
CTE2 AS (SELECT COUNT(login) FROM userinfo AS Col2)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

I receive the following errors:

Msg 8155, Level 16, State 2, Line 1
No column name was specified for column 1 of 'CTE1'.
Msg 8155, Level 16, State 2, Line 2
No column name was specified for column 1 of 'CTE2'.

Are there limitations when trying to use multiple CTE in a single query?

Thank you!!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-05-08 : 11:18:32

The syntax you are using for aliasing is not quite right. Use one of the two methods shown below:

--- 1
;WITH CTE1 AS (SELECT COUNT(login) AS Col1 FROM userinfo),
CTE2 AS (SELECT COUNT(login) AS Col2 FROM userinfo)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2
GO

--- 2
;WITH CTE1(Col1) AS (SELECT COUNT(login) FROM userinfo),
CTE2(Col2) AS (SELECT COUNT(LOGIN) FROM userinfo)
SELECT CTE1.Col1,CTE2.Col2
FROM CTE1
CROSS JOIN CTE2

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-08 : 11:18:44

IN the second example, you are aliasing the table, not the columns, Instead of:


... CTE1 AS (SELECT COUNT(login) FROM userinfo AS Col1),

try


...CTE1 AS (SELECT COUNT(login) as COL1 FROM userinfo),

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-05-08 : 11:50:08

Thanks for the quick response guys. You Rock!

I can get the simple example I have provided to work from a single table, but my 2 select statements are not as straight forward as the ones I have provided. Both selects are very similar, with slightly different criteria but from 13 different tables. Both queries work independently of one another, so I do not have any syntax errors. I have fixed the column aliases as per your instructions, which makes perfect sense, but I am now receiving the following error:

Msg 1016, Level 15, State 1, Line 131
Outer join operators cannot be specified in a query containing joined tables.

Are there limitations with a single query that uses multiple CTEs that use multiple inner joined tables?

Thanks again guys!!!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-05-08 : 12:34:23

Can you post the whole query?

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-05-08 : 15:01:12

Actually, I was able to figure it out on my own. I just had to get rid of the CROSS JOIN in the last statement, like so...

SELECT CTE1.Col1,CTE2.Col2
FROM CTE1, CTE2

Thanks again, though, for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources