Incorrect sentax union Keyword in Sql 2000

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 sentax union Keyword in Sql 2000

Author

Topic

ermjai84
Starting Member

4 Posts

Posted - 2008-07-14 : 00:06:21

Hi,

I have a problem in sql server 2000. My problem is i am using Multiple table and Group by funcion, That is working but i will use union function so i got error is Incorrect Sentax Union Keyword,so what i do please anybody helpme

This is my code

select count(a.ackno) as macno,
sum(a.amount) as ssmount,
sum(a.netrevenue1) as netrevenue1,
sum(a.netrevenue2) as netrevenue2,
b.brname as brname ,
a.empname1 as empname1,
a.empname2 as empname2,
a.amount1 as amount1,
a.amount2 as amount2,
c.prodgroup as prodgroup,
a.chdddate as chddate,
a.passoutval as passoutval

from tranmast a,branmast b,prodmast c

where a.brcode=b.brcode and a.prodcode=c.prodcode and a.brcode=any(select brcode from userright where empid=@empid)
and (a.chdddate between convert(datetime,@fromdate,103)
and convert(datetime,@todate,103)) and a.transtatus='Complete' and a.brcode=any(select brcode from userright where empid=@empid)

group by b.brname ,a.empname1,a.empname2 ,a.chdddate,a.amount1,a.amount2,c.prodgroup ,a.passoutval
order by c.prodgroup,a.empname1,a.empname2

Union

select count(a.ackno) as macno,
sum(a.amount) as ssmount,
sum(a.netrevenue1) as netrevenue1,
sum(a.netrevenue2) as netrevenue2,
b.brname as brname ,
a.empname1 as empname1,
a.empname2 as empname2,
a.amount1 as amount1,
a.amount2 as amount2,
c.prodgroup as prodgroup,
a.chdddate as chddate,
a.passoutval as passoutval

from tranmast a,branmast b,prodmast c

where a.brcode=b.brcode and a.prodcode=c.prodcode and a.brcode=any(select brcode from userright where empid=@empid)
and (a.trandate between convert(datetime,@fromdate,103)
and convert(datetime,@todate,103)) and a.transtatus='Complete' and a.brcode=any(select brcode from userright where empid=@empid)

group by b.brname ,a.empname1,a.empname2 ,a.chdddate,a.amount1,a.amount2,c.prodgroup ,a.passoutval
order by c.prodgroup,a.empname1,a.empname2

Jayakumar

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-14 : 00:10:52

remove the order by in the 1st query

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