470,596 Members | 1,314 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,596 developers. It's quick & easy.

Scope in derived tables

This is kind of what I'm trying to do in my MS SQL 2000 query. Should
I be able to reference s1.col1 inside the 2nd derived table?

I'm getting 'Invalid column name col1' and it's coming from the 2nd
derived table (I've commented out other refs to just it to check).

Maybe I need to use a temp table instead.
SELECT s1.col1,

(SELECT * FROM

(SELECT COUNT(zzz) AS SomeTotal
FROM tab1
WHERE s1.col1 = zzz)) AS RowCount) /* error here */

FROM
(SELECT col1 FROM table) AS s1
Thank you!

Jun 29 '07 #1
1 3009
Stephen2 (St*****@mailinator.com) writes:
This is kind of what I'm trying to do in my MS SQL 2000 query. Should
I be able to reference s1.col1 inside the 2nd derived table?

I'm getting 'Invalid column name col1' and it's coming from the 2nd
derived table (I've commented out other refs to just it to check).

Maybe I need to use a temp table instead.
SELECT s1.col1,

(SELECT * FROM

(SELECT COUNT(zzz) AS SomeTotal
FROM tab1
WHERE s1.col1 = zzz)) AS RowCount) /* error here */

FROM
(SELECT col1 FROM table) AS s1
Yes, you should be able to, and in SQL 2005 you are. But in SQL 2000 there
is a bug that gets in the way. It seems that you will have to resort to
a temp table.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Jun 29 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by bardos | last post: by
3 posts views Thread by richard pickworth | last post: by
1 post views Thread by Chris Dunaway | last post: by
1 post views Thread by Ruediger Herrmann | last post: by
9 posts views Thread by olanglois | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.