Cannot reference derived table from a derived table in a subquery?

The follow generates the error:
Server: Msg 207, Level 16, State 3, Line 19
Invalid column name 'UserID'.
The inner derived table ("dt") cannot reference the outer derived table
("ActiveUsers") ?
Nevermind the contrived example - it is only serving to illustrate the
failing. The real situation is entirely more complex.
IF OBJECT_ID('tempdb..#Users') IS NOT NULL
DROP TABLE #Users
IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
DROP TABLE #Logins
CREATE TABLE #Users (
UserID int,
Name varchar(50),
IsActive tinyint)
CREATE TABLE #Logins (
UserID int,
LoginDate datetime,
WasSecured tinyint)
SELECT
ActiveUsers.*,
( SELECT MAX(LoginDate)
FROM (
SELECT * FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) dt
) AS LastSecuredLogin
FROM (
SELECT *
FROM #Users
WHERE IsActive <> 0
) ActiveUsers
DROP TABLE #Users
DROP TABLE #LoginsTry,
SELECT
ActiveUsers.*,
l.dt AS LastSecuredLogin
FROM
#Users as ActiveUsers
left join
(
SELECT
#Logins.UserID,
MAX(LoginDate) as dt
FROM
#Logins
WHERE
#Logins.WasSecured <> 0
group by
#Logins.UserID
) as l
on l.UserID = ActiveUsers.UserID
WHERE
ActiveUsers.IsActive <> 0
go
AMB
"Ian Boyd" wrote:

> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||Right, in your correlated subquery, you have a derived table that references
something outside of the scope of the derived table. Basically you have to
think of derived tables as views. They cannot reference anything from the
current query.
Maybe I am being a bit of a thicky, but I don't see why:
( SELECT MAX(LoginDate)
FROM ( SELECT *
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0 ) dt
) AS LastSecuredLogin
cannot be rewritten as:
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
The only reason to do what you were doing (I think) is if you were going to
do an aggregate on an aggregate. The where clause and the groupings are
exclusive of one another, so it should work. Am I missing something?
Full Query:
SELECT
ActiveUsers.*,
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
FROM ( SELECT *
FROM #Users
WHERE IsActive <> 0 ) as ActiveUsers
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:u462TqHCFHA.3740@.TK2MSFTNGP09.phx.gbl...
> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||> Maybe I am being a bit of a thicky, but I don't see why:
Becuase this wasn't my situation. The two tables is a contrived example -
boiled down to still cause the problem.
The "inner" derived table query's 3 tables unioned to 3 more tables.
The "outer" derived table query 3 more separate tables.|||Ah, I thought you weren't going to give us a contrived example :) I see.
Anyhow, you cannot use derived tables like that. You might look at user
defined functions if Alejandro's suggestion doesn't work. You can do a lot
of stuff in there. They work very well in the SELECT clause. Or post
your entire query and who knows, someone may have done the same thing in the
past.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23tnVDIICFHA.3740@.TK2MSFTNGP09.phx.gbl...
> Becuase this wasn't my situation. The two tables is a contrived example -
> boiled down to still cause the problem.
> The "inner" derived table query's 3 tables unioned to 3 more tables.
> The "outer" derived table query 3 more separate tables.
>
>|||The query has been restructured; and it eliminates the reference from one
derived table to the other.

> Or post your entire query and who knows, someone may have done the same
> thing in the past.
Absoutly not. Nobody has ever done anything like it. The query is 3 screens
long.
And i'm not going to be posting DDL, sample inserts, or explanations.
Or the query :)|||> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
Well, not sure how long a screen is, but I am sure you are unlikely to be
the first person to do "anything like it."

> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
Then good luck, can't help you out if we can't see what you are doing. :)
Either way, your derived table must be self contained.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:Oa3IGfICFHA.3644@.TK2MSFTNGP15.phx.gbl...
> The query has been restructured; and it eliminates the reference from one
> derived table to the other.
>
> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
>sql