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!

Re: Scope in derived tables

Stephen2 (Stephen@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx