"No column was specified" on a subquery ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

"No column was specified" on a subquery ?

Author

Topic

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-09-15 : 11:31:36

Msg 8155, Level 16, State 2, Line 11
No column was specified for column 8 of 't'.

select rl.shipname, rl.saildate, rl.itinerary, rl.deck, rl.cabinnumber, rl.cabinclass, rl.cabinprice, rl.executiontime, rl.id_num, rl.subscriptionname
from royalinvc as rl
left outer join 

(select shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, max(executiontime), id_num, subscriptionname
from royalinvc
group by shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, executiontime, id_num, subscriptionname)t
on rl.cabinnumber = t.cabinnumber where t.cabinnumber is null

group by rl.cabinnumber
order by executiontime desc, shipname, saildate, itinerary, cabinclass, deck,  cabinprice

but the column is right there in max(executiontime). Basically I'm trying to remove the entries corresponding to most updated executiontime. So I did a max(executiontime) in the subquery, and I'm trying subtract it from outer query.

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-09-15 : 11:36:00

got it

needs to be named

max(executiontime) AS EXECUTIONTIME

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-15 : 11:39:17

Which is column 8 in the derived table, right?

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 12:24:44

i think what you need is this

select rl.shipname, rl.saildate, rl.itinerary, rl.deck, rl.cabinnumber, rl.cabinclass, rl.cabinprice, rl.executiontime, rl.id_num, rl.subscriptionname
from royalinvc as rl
left outer join 

(select shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, max(executiontime), id_num, subscriptionname
from royalinvc
group by shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, executiontime, id_num, subscriptionname)t
on rl.cabinnumber = t.cabinnumber 
and r1.shipname=t.shipname
and r1.saildate=t.saildate
and r1.itinerary=t.itinerary
and r1.deck=t.deck
and r1.cabinclass=t.cabinclass
and r1.cabinprice=t.cabinprice
and r1.executiontime=t.EXECUTIONTIME
and id_num=id_num
and subscriptionname=subscriptionname
where t.cabinnumber is null

group by rl.cabinnumber
order by executiontime desc, shipname, saildate, itinerary, cabinclass, deck,  cabinprice

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-09-15 : 12:51:26

shipname saildate itinerary deck cabinnumber cabinclass cabinprice executiontime
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/11/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/11/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/10/2008 6:00:01 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/10/2008 6:00:01 AM

hmm, Visahk, I think you understand what I want to do already. For a given ship and saildate, I need to isolate each roomtype, and take the most recent executiontime of that roomtype.

So for the above, I need...

Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/11/2008 6:00:04 AM

I tried your query but got this error...


Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.shipname" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.saildate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.itinerary" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.deck" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.cabinclass" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.cabinprice" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.executiontime" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.subscriptionname" could not be bound.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 12:55:31

quote:
Originally posted by sqlchiq

shipname saildate itinerary deck cabinnumber cabinclass cabinprice executiontime
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/11/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/11/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/10/2008 6:00:01 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/10/2008 6:00:01 AM

hmm, Visahk, I think you understand what I want to do already. For a given ship and saildate, I need to isolate each roomtype, and take the most recent executiontime of that roomtype.

So for the above, I need...

Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/11/2008 6:00:04 AM

I tried your query but got this error...

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.shipname" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.saildate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.itinerary" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.deck" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.cabinclass" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.cabinprice" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.executiontime" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "r1.subscriptionname" could not be bound.

ah my bad sight...seems like i need a fresh pair of glasses. that was rl not r1...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 12:58:01

Also you might need a distinct as you've duplicate records coming for same max execution date

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-09-15 : 13:06:56

i forgot the case of the id_num

shipname saildate itinerary deck cabinnumber cabinclass cabinprice executiontime
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM 1
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM 2
Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/11/2008 6:00:04 AM 3
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/11/2008 6:00:04 AM 4
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/10/2008 6:00:01 AM 5
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/10/2008 6:00:01 AM 6

so it should be

So for the above, I need...

Radiance 2008-12-07 Sao Paulo Cruise 7 7672 Royal Family Suite 8/12/2008 6:00:04 AM 1
Radiance 2008-12-07 Sao Paulo Cruise 8 8672 Royal Family Suite 8/11/2008 6:00:04 AM 4

also on that query, it results...
Msg 8120, Level 16, State 1, Line 1
Column 'royalinvc.shipname' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

It will say that for every column in the select, should I just go ahead and add it to the group?

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-09-15 : 13:14:51

I added everything in the select to that group by

but it results 0 entries

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-15 : 13:15:16

just use like below

select rl.shipname, rl.saildate, rl.itinerary, rl.deck, rl.cabinnumber, rl.cabinclass, rl.cabinprice, rl.executiontime, rl.id_num, rl.subscriptionname
from royalinvc as rl
left outer join 

(select shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, executiontime, max(id_num) as id_num, subscriptionname
from royalinvc
group by shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, executiontime,subscriptionname)t
on rl.cabinnumber = t.cabinnumber 
and rl.shipname=t.shipname
and rl.saildate=t.saildate
and rl.itinerary=t.itinerary
and rl.deck=t.deck
and rl.cabinclass=t.cabinclass
and rl.cabinprice=t.cabinprice
and rl.executiontime=t.executiontime
and rl.id_num=t.id_num
and rl.subscriptionname=t.subscriptionname
where t.cabinnumber is null
order by executiontime desc, shipname, saildate, itinerary, cabinclass, deck,  cabinprice

sqlchiq
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 09:58:02

quote:

Originally posted by visakh16

just use like below

select rl.shipname, rl.saildate, rl.itinerary, rl.deck, rl.cabinnumber, rl.cabinclass, rl.cabinprice, rl.executiontime, rl.id_num, rl.subscriptionname
from royalinvc as rl
left outer join 

(select shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, executiontime, max(id_num) as id_num, subscriptionname
from royalinvc
group by shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, executiontime,subscriptionname)t
on rl.cabinnumber = t.cabinnumber 
and rl.shipname=t.shipname
and rl.saildate=t.saildate
and rl.itinerary=t.itinerary
and rl.deck=t.deck
and rl.cabinclass=t.cabinclass
and rl.cabinprice=t.cabinprice
and rl.executiontime=t.executiontime
and rl.id_num=t.id_num
and rl.subscriptionname=t.subscriptionname
where t.cabinnumber is null
order by executiontime desc, shipname, saildate, itinerary, cabinclass, deck,  cabinprice

Query works but its not quite working 100% correctly

I'm getting entries with duplicates such as below

shipname saildate itinerary deck cabinnumber cabinclass cabinprice executiontime id_num subscriptionname
indepen 2009-02-15 cari 6 6583 Promenade 2030.10 2008-07-19 Royal cari indepen
indepen 2009-02-15 cari 6 6583 Promenade 2030.10 2008-07-18 122537 Royal cari indepen
indepen 2009-02-15 cari 6 6583 Promenade 2030.10 2008-07-17 115770 Royal cari indepen
indepen 2009-02-15 cari 6 6583 Promenade 2030.10 2008-07-16 108787 Royal cari indepen

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 10:02:45

if you want one record out of posted ones. use this:-

select rl.shipname, rl.saildate, rl.itinerary, rl.deck, rl.cabinnumber, rl.cabinclass, rl.cabinprice, rl.executiontime, rl.id_num, rl.subscriptionname
from royalinvc as rl
left outer join 

(select shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, max(executiontime) as maxtime, max(id_num) as id_num, subscriptionname
from royalinvc
group by shipname, saildate, itinerary, deck, cabinnumber, cabinclass, cabinprice, subscriptionname)t
on rl.cabinnumber = t.cabinnumber 
and rl.shipname=t.shipname
and rl.saildate=t.saildate
and rl.itinerary=t.itinerary
and rl.deck=t.deck
and rl.cabinclass=t.cabinclass
and rl.cabinprice=t.cabinprice
and rl.executiontime=t.maxtime
and rl.id_num=t.id_num
and rl.subscriptionname=t.subscriptionname
where t.cabinnumber is null
order by rl.executiontime desc, rl.shipname, rl.saildate, rl.itinerary, rl.cabinclass, rl.deck,  rl.cabinprice

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources