Incorrect syntax near the keyword 'union'

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

Incorrect syntax near the keyword 'union'

Author

Topic

feejaz
Yak Posting Veteran

68 Posts

Posted - 2008-07-01 : 09:42:35

HI ALL,

I HAVE CREATED A QUERY LIKE BELOW AND FOUND ERROR
Incorrect syntax near the keyword 'union'.

select top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where CheckCall.unitid= 5
order by CallDate desc
union
SELECT top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where Call.unitid= 7
order by CallDate desc
BUT WHEN I COMMIT THE FIRST ORDER BY COMMAND THE ERROR IS CLEAR BUT THE RECORD ARE NOT IN DESC ORDER.

ANY HELP.

Navi

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 09:44:37

[code]
select *
from
(
select top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where CheckCall.unitid= 5
order by CallDate desc
) a
union
select *
from
(
SELECT top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where Call.unitid= 7
order by CallDate desc
) b[/code]

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-01 : 09:47:50

duplicate thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=105847

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-01 : 12:57:23

quote:

Originally posted by khtan


select *
from
(
select top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where CheckCall.unitid= 5
order by CallDate desc
) a
union
select *
from
(
SELECT top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where Call.unitid= 7
order by CallDate desc 
) b

KH
[spoiler]Time is always against us[/spoiler]

is there really a need of union here? is this equivalent to

SELECT  [Call].*, unit.unitname FROM [Call] c
inner join (select pk,max(calldate) as maxdate from call group by pk)t
on t.pk=c.pk
and t.maxdate=c.calldate
left join unit u on c.unitid=u.unitid
where c.unitid in(5,7)

where pk is primary key of call table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-01 : 14:53:47

quote:

Originally posted by visakh16

quote:

Originally posted by khtan


select *
from
(
select top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where CheckCall.unitid= 5
order by CallDate desc
) a
union
select *
from
(
SELECT top 1 [Call].*, unit.unitname FROM [Call]
left join unit on [Call].unitid=unit.unitid
where Call.unitid= 7
order by CallDate desc 
) b

KH
[spoiler]Time is always against us[/spoiler]

is there really a need of union here? is this equivalent to

SELECT  [Call].*, unit.unitname FROM [Call] c
inner join (select pk,max(calldate) as maxdate from call group by pk)t
on t.pk=c.pk
and t.maxdate=c.calldate
left join unit u on c.unitid=u.unitid
where c.unitid in(5,7)

where pk is primary key of call table

Also add

order by c.unitid, c.calldate desc

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources