|
Home > Archive > microsoft.public.sqlserver.server > November 2002 > Please help: incorrect syntax
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Please help: incorrect syntax
|
|
|
| Hi there,
I have this stored procedure in a database VMaster which should retrieve
records from tblAct in VMaster together with records from the same tblAct in
another database called Vx:
DECLARE @KlantCode char(6), @VertCode char(2)
SET @KlantCode='010001' /* hardcoded for testing only */
SET @VertCode='01'
DECLARE @Query nvarchar(4000)
SET @Query =
'SELECT act.Afgewerkt, typ.Icoon, doc.DocID, doc.Extensie, act.Datum,' +
' typ.Omschrijving AS Type, act.Omschrijving, gebr.Naam AS Gebruiker,
act.ActID, act.ActType' +
' FROM tblAct act' +
' LEFT OUTER JOIN tblActDoc doc ON doc.ActCode = act.ActID' +
' LEFT OUTER JOIN tblActTypes typ ON typ.ActTypeID = act.ActType' +
' LEFT OUTER JOIN tblGebruikers gebr ON gebr.GebrID = act.ActIDPrefix' +
' WHERE act.KlantCode = ' + @KlantCode +
' ORDER BY act.Afgewerkt, act.Datum ASC'
SET @Query = @Query + ' UNION ' +
'SELECT act.Afgewerkt, typ.Icoon, doc.DocID, doc.Extensie, act.Datum,' +
' typ.Omschrijving AS Type, act.Omschrijving, gebr.Naam AS Gebruiker,
act.ActID, act.ActType' +
' FROM V' + @VertCode + '.dbo.tblAct act' +
' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblActDoc doc ON doc.ActCode =
act.ActID' +
' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblActTypes typ ON typ.ActTypeID =
act.ActType' +
' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblGebruikers gebr ON gebr.GebrID
= act.ActIDPrefix' +
' WHERE act.KlantCode = ' + @KlantCode +
' ORDER BY act.Afgewerkt, act.Datum ASC'
EXECUTE(@Query)
When I comment out the second query, it works. When I comment out the first,
the second works too. It's the whole that doesn't work and results in:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'UNION'.
Both tables have the same structure, so the UNION should not be a problem,
and indeed the following does work:
SELECT * FROM tblAct
UNION
SELECT * FROM V01.dbo.tblAct
Maybe I'm blind, but I can't see what's wrong in the above code, anybody
can...?
Thanks a lot,
--
SLE
| |
| Dejan Sarka 2002-11-28, 3:23 am |
| You can have only one Order By clause per query, when using Union,
everything is treated as a single query. Order By can be specified in the
last Select only.
--
Dejan Sarka, SQL Server MVP
FAQ from Neil & others at: http://www.sqlserverfaq.com
Please reply only to the newsgroups.
PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"SLE" <infoNOSPAM@proit.be> wrote in message
news:3de5d0ee$0$221$ba620e4c@n
ews.skynet.be...
> Hi there,
>
> I have this stored procedure in a database VMaster which should retrieve
> records from tblAct in VMaster together with records from the same tblAct
in
> another database called Vx:
>
> DECLARE @KlantCode char(6), @VertCode char(2)
>
> SET @KlantCode='010001' /* hardcoded for testing only */
> SET @VertCode='01'
>
> DECLARE @Query nvarchar(4000)
>
> SET @Query =
> 'SELECT act.Afgewerkt, typ.Icoon, doc.DocID, doc.Extensie, act.Datum,' +
> ' typ.Omschrijving AS Type, act.Omschrijving, gebr.Naam AS Gebruiker,
> act.ActID, act.ActType' +
> ' FROM tblAct act' +
> ' LEFT OUTER JOIN tblActDoc doc ON doc.ActCode = act.ActID' +
> ' LEFT OUTER JOIN tblActTypes typ ON typ.ActTypeID = act.ActType' +
> ' LEFT OUTER JOIN tblGebruikers gebr ON gebr.GebrID = act.ActIDPrefix' +
> ' WHERE act.KlantCode = ' + @KlantCode +
> ' ORDER BY act.Afgewerkt, act.Datum ASC'
>
> SET @Query = @Query + ' UNION ' +
> 'SELECT act.Afgewerkt, typ.Icoon, doc.DocID, doc.Extensie, act.Datum,' +
> ' typ.Omschrijving AS Type, act.Omschrijving, gebr.Naam AS Gebruiker,
> act.ActID, act.ActType' +
> ' FROM V' + @VertCode + '.dbo.tblAct act' +
> ' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblActDoc doc ON doc.ActCode =
> act.ActID' +
> ' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblActTypes typ ON typ.ActTypeID
=
> act.ActType' +
> ' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblGebruikers gebr ON
gebr.GebrID
> = act.ActIDPrefix' +
> ' WHERE act.KlantCode = ' + @KlantCode +
> ' ORDER BY act.Afgewerkt, act.Datum ASC'
>
> EXECUTE(@Query)
>
>
> When I comment out the second query, it works. When I comment out the
first,
> the second works too. It's the whole that doesn't work and results in:
>
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'UNION'.
>
> Both tables have the same structure, so the UNION should not be a problem,
> and indeed the following does work:
>
> SELECT * FROM tblAct
> UNION
> SELECT * FROM V01.dbo.tblAct
>
> Maybe I'm blind, but I can't see what's wrong in the above code, anybody
> can...?
>
> Thanks a lot,
>
> --
> SLE
>
>
| |
| Tibor Karaszi 2002-11-28, 4:23 am |
| One thing is that you can't have ORDER BY in the individual queries in a UNION. You can have
ORDER BY after the last query in the UNIONed queries.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"SLE" <infoNOSPAM@proit.be> wrote in message news:3de5d0ee$0$221$ba620e4c@n
ews.skynet.be...
> Hi there,
>
> I have this stored procedure in a database VMaster which should retrieve
> records from tblAct in VMaster together with records from the same tblAct in
> another database called Vx:
>
> DECLARE @KlantCode char(6), @VertCode char(2)
>
> SET @KlantCode='010001' /* hardcoded for testing only */
> SET @VertCode='01'
>
> DECLARE @Query nvarchar(4000)
>
> SET @Query =
> 'SELECT act.Afgewerkt, typ.Icoon, doc.DocID, doc.Extensie, act.Datum,' +
> ' typ.Omschrijving AS Type, act.Omschrijving, gebr.Naam AS Gebruiker,
> act.ActID, act.ActType' +
> ' FROM tblAct act' +
> ' LEFT OUTER JOIN tblActDoc doc ON doc.ActCode = act.ActID' +
> ' LEFT OUTER JOIN tblActTypes typ ON typ.ActTypeID = act.ActType' +
> ' LEFT OUTER JOIN tblGebruikers gebr ON gebr.GebrID = act.ActIDPrefix' +
> ' WHERE act.KlantCode = ' + @KlantCode +
> ' ORDER BY act.Afgewerkt, act.Datum ASC'
>
> SET @Query = @Query + ' UNION ' +
> 'SELECT act.Afgewerkt, typ.Icoon, doc.DocID, doc.Extensie, act.Datum,' +
> ' typ.Omschrijving AS Type, act.Omschrijving, gebr.Naam AS Gebruiker,
> act.ActID, act.ActType' +
> ' FROM V' + @VertCode + '.dbo.tblAct act' +
> ' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblActDoc doc ON doc.ActCode =
> act.ActID' +
> ' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblActTypes typ ON typ.ActTypeID =
> act.ActType' +
> ' LEFT OUTER JOIN V' + @VertCode + '.dbo.tblGebruikers gebr ON gebr.GebrID
> = act.ActIDPrefix' +
> ' WHERE act.KlantCode = ' + @KlantCode +
> ' ORDER BY act.Afgewerkt, act.Datum ASC'
>
> EXECUTE(@Query)
>
>
> When I comment out the second query, it works. When I comment out the first,
> the second works too. It's the whole that doesn't work and results in:
>
> Server: Msg 156, Level 15, State 1, Line 2
> Incorrect syntax near the keyword 'UNION'.
>
> Both tables have the same structure, so the UNION should not be a problem,
> and indeed the following does work:
>
> SELECT * FROM tblAct
> UNION
> SELECT * FROM V01.dbo.tblAct
>
> Maybe I'm blind, but I can't see what's wrong in the above code, anybody
> can...?
>
> Thanks a lot,
>
> --
> SLE
>
>
| |
|
| "Dejan Sarka" < dejan_please_reply_to_newsgrou
ps.sarka@reproms.si> schreef in
bericht news:#lwNl7rlCHA.1284@tkmsftngp02...
> You can have only one Order By clause per query, when using Union,
> everything is treated as a single query. Order By can be specified in the
> last Select only.
>
Thanks, that was the problem indeed!
[I wish SQL Server issued a more specific error :-))]
--
SLE
|
|
|
|
|