subselect, please help - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

subselect, please help

Author

Topic

vux
Starting Member

45 Posts

Posted - 2004-07-12 : 02:25:34

Hello, I am siiting here for hours and don;t know what is wrong.

this is the situation

TABLE Travel:

TravelID SeqNum Arruval DepDate

1 1 a d
1 2 b e
1 3 c f

I want the Departure/Arrival dates c/f because it is the latest entry. this line will be written in to another table where TravelID should be the primary key - so multiple entries for TravelID should be avoided

The querry looks like that:

SELECT
tvrq0_extract.tvrq_doc_id AS 'Travel ID',

(SELECT itnh0_extract.itnh_f_tvrq_doc_id, itnh0_extract.itnh_deprt_date
FROM itnh0_extract
WHERE (((itnh0_extract.itnh_f_tvrl_seq_num)=(SELECT MAX(itnh0_extract.itnh_f_tvrl_seq_num) FROM itnh0_extract ali WHERE itnh0_extract.itnh_f_tvrq_doc_id = ali.itnh_f_tvrq_doc_id ))))AS 'Departure Date',

(SELECT itnh0_extract.itnh_f_tvrq_doc_id, itnh0_extract.itnh_retrn_date
FROM itnh0_extract
WHERE (((itnh0_extract.itnh_f_tvrl_seq_num)=(SELECT MAX(itnh0_extract.itnh_f_tvrl_seq_num) FROM itnh0_extract ali WHERE itnh0_extract.itnh_f_tvrq_doc_id = ali.itnh_f_tvrq_doc_id ))))AS 'Return Date',

FROM bla bla

WHERE bla bla (joints)

The SQL querry analyzer successfully parses the query, but when I want to run the querry in the SQL Enterprise Manager I get syntax errors.

So any hints?

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 02:28:58

Not sure if the query itself will solve your problem but you can't have a comma after 'Return Date' before FROM

--Ken
Your Kung-Fu is not strong. -- 'The Core'

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-12 : 08:31:28

You have a few problems here besides the comma. In your subqueries in the SELECT statement, you are trying to return multiple columns. You can only return one column in those subqueries. After you take care of that problem, you need to rewrite it so these subqueries are derived tables in the FROM clause anyway since you're doing the same thing for both of them. If you don't know what this means, look up subquery and derived table in Books Online. Hopefully, this will get you started in the right direction. If you need more help, let us know.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

vux
Starting Member

45 Posts

Posted - 2004-07-12 : 23:31:08

thank for your advise, this is all new are for me,

someone gave me a suggestion in another thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37350

there he is actually also selecting more than one collun in the subselect

select a.TravelID, a.Seqnb, a.dep, a.arr, a.name
from tablex a
join (select name, max(seqnb) from tablex group by name) as b
on a.name = b.name
and a.seqnb = b.seqnb

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-07-12 : 23:36:11

Your queries are subselects because they occur in the SELECT <expression> part of the query. In the second example, those are derived tables that occur in the FROM clause of the query. You can have multiple columns in a derived table (just like you could have multiple columns in any other table). A subselect must return a single value because it takes the place of a single column in the output.

--Ken
Your Kung-Fu is not strong. -- 'The Core'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources