?
Solved

Invalid Column Name

Posted on 2009-04-20
6
Medium Priority
?
823 Views
Last Modified: 2012-08-13
[SQL 2000] - Why am I getting this error on the query below.  Can I not join on an aliased a column in a subquery?

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'minDt'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'maxDt'.

select * from 
    (
       select X, min(datefield) as minDt
       from   T1
       group  by X
       having count(*) > 5
     ) T1
join (
       select X, max(datefield) as maxDt
       from   T2
       group  by X
       having count(*) > 5
     ) T2
  on T2.X = T1.X
 and T2.minDt > dateadd(minute,-30,T1.maxDt)

Open in new window

0
Comment
Question by:knightEknight
6 Comments
 
LVL 41

Accepted Solution

by:
ralmada earned 360 total points
ID: 24187155
They are inverted. Try like this:
select * from 
    (
       select X, min(datefield) as minDt
       from   T1
       group  by X
       having count(*) > 5
     ) T1
join (
       select X, max(datefield) as maxDt
       from   T2
       group  by X
       having count(*) > 5
     ) T2
  on T2.X = T1.X
 and T1.minDt > dateadd(minute,-30,T2.maxDt)

Open in new window

0
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 320 total points
ID: 24187162
minDt is from T1, and maxDt is from T1.

:)
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 24187164
too slow :)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 320 total points
ID: 24187165
> T2.minDt
is wrong, as minDt is in T1, T2 has maxDT

>T1.maxDt
is wrong, as maxDt is in T2, T1 has MinDt

please double-check your logic
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24187166
PS: I've just updated line 15 (MinDT is from T1 and MaxDt is from T2) so it's like this:
 and T1.minDt > dateadd(minute,-30,T2.maxDt)
 
0
 
LVL 33

Author Closing Comment

by:knightEknight
ID: 31572370
duh!  Thanks guys, just needed the extra eyes.  :)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question