Nested select problem

[taphonomist]

I get syntax errors with this

select but
from
(select
bte.buildfile, max(bt.buildtime) as but
from buildtimeestimator bte, buildtime bt
where bte.buildfile=bt.buildfile
group by bte.buildfile)

while this

select
bte.buildfile, max(bt.buildtime) as but
from buildtimeestimator bte, buildtime bt
where bte.buildfile=bt.buildfile
group by bte.buildfile

works fine. What's going on???

taphonomist

 

[Sunny4Ever]

You are trying to select a table and this is not supported and will fail! You can only use select to select specific fields or by using the * all fields.

in your code 'but' is a derived table with 2 columns. You could use:

select *
from
(select
bte.buildfile, max(bt.buildtime) as but
from buildtimeestimator bte, buildtime bt
where bte.buildfile=bt.buildfile
group by bte.buildfile)

 

[taphonomist]

but is the second column in the two column table, the first being bte.buildfile (see the group by statement). In any case, I've also tried the code you've written with no luck.

taphonomist

 

[r937]

whenever you have a derived table, you have to give it an alias

change your first query as follows --

select but
  from (
       select bte.buildfile
            , max(bt.buildtime) as but
         from buildtimeestimator bte
            , buildtime bt
        where bte.buildfile
            = bt.buildfile
       group 
           by bte.buildfile
       ) [b]as foo[/b]

r937.com | rudy.ca

 

[taphonomist]

Thanks much!! That'll do. It seems kind of odd that you have to give an alias to "transient" data... oh well, as long as it works!!

taphonomist

 

[Sunny4Ever]

Ooops - sorry misread the brackets. How about putting a final 'AS Newtable' at the very end?

 

[taphonomist]

No worries, thanks to you both for the help!

taphonomist