Syntax error using UNION ALL - 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

General SQL Server Forums

New to SQL Server Programming

Syntax error using UNION ALL

Author

Topic

msmithgp
Starting Member

2 Posts

Posted - 2011-10-29 : 17:14:08

I wrote a select query from one database and am trying to union that query with an identical query on another database. The columns I received the following error.

Incorrect syntax near the keyword 'UNION'.

What can I do to resolve this issue and allow the query to union both databases of info?



SELECT  
	'ACC' AS [Site],
	isnull(convert(varchar, rtrim([T2].[ACTNUMBR_1]) + '-' + rtrim([T2].[ACTNUMBR_2]) + '-' + rtrim([T2].[ACTNUMBR_3])),'Grand Total') as [GL Account],
    [T1].[CRDTAMNT] as Credit,
    [T1].[DEBITAMT] as Debit,
      -[T1].[CRDTAMNT]+[T1].[DEBITAMT] as [+DR-CR],
       [T2].[ACTDESCR] as [Account Desc]
      ,[T1].[JRNENTRY] as [Journal Entry]
      ,[T1].[SOURCDOC] as [Source Doc]
      ,[T1].[REFRENCE] as [Reference]
      ,[T1].[DSCRIPTN] as [Description]
      ,convert(varchar,([T1].[TRXDATE]),101) as [Trx Date]
      ,[T1].[TRXSORCE] as [Trx Source]
      ,[T1].[USWHPSTD] as [Posted User]
      ,[T1].[ORGNTSRC] 
      ,[T1].[ORCTRNUM]
      ,[T1].[ORMSTRID]
      ,[T1].[ORMSTRNM]
      ,[T1].[ORDOCNUM] as [OR Doc Num]
      ,convert(varchar,[T1].[ORPSTDDT],101) as [OR PST Date]
      ,[T1].[ORTRXSRC] as [OR Trx Source]
      ,[T1].[ORCOMID]
      ,[T1].[ORIGINJE]
      ,[T1].[PERIODID] as [Period ID]
      ,[T1].[CRDTAMNT] as [Credit Amount]
      ,[T1].[DEBITAMT] as [Debit Amount]
      ,[T1].[VOIDED] as [Voided]

FROM [acc01].[dbo].[GL20000] T1

LEFT OUTER JOIN [acc01].[dbo].[GL00100] T2
ON [T1].[ACTINDX] = [T2].[ACTINDX]
WHERE rtrim([T2].[ACTNUMBR_1]) + '-' + rtrim([T2].[ACTNUMBR_2]) + '-' + rtrim([T2].[ACTNUMBR_3]) LIKE ('2160-0000-C')
Order by [GL Account]

UNION ALL

SELECT  
	'AJC' AS [Site],
	isnull(convert(varchar, rtrim([T2].[ACTNUMBR_1]) + '-' + rtrim([T2].[ACTNUMBR_2]) + '-' + rtrim([T2].[ACTNUMBR_3])),'Grand Total') as [GL Account],
    [T1].[CRDTAMNT] as Credit,
    [T1].[DEBITAMT] as Debit,
      -[T1].[CRDTAMNT]+[T1].[DEBITAMT] as [+DR-CR],
       [T2].[ACTDESCR] as [Account Desc]
      ,[T1].[JRNENTRY] as [Journal Entry]
      ,[T1].[SOURCDOC] as [Source Doc]
      ,[T1].[REFRENCE] as [Reference]
      ,[T1].[DSCRIPTN] as [Description]
      ,convert(varchar,([T1].[TRXDATE]),101) as [Trx Date]
      ,[T1].[TRXSORCE] as [Trx Source]
      ,[T1].[USWHPSTD] as [Posted User]
      ,[T1].[ORGNTSRC] 
      ,[T1].[ORCTRNUM]
      ,[T1].[ORMSTRID]
      ,[T1].[ORMSTRNM]
      ,[T1].[ORDOCNUM] as [OR Doc Num]
      ,convert(varchar,[T1].[ORPSTDDT],101) as [OR PST Date]
      ,[T1].[ORTRXSRC] as [OR Trx Source]
      ,[T1].[ORCOMID]
      ,[T1].[ORIGINJE]
      ,[T1].[PERIODID] as [Period ID]
      ,[T1].[CRDTAMNT] as [Credit Amount]
      ,[T1].[DEBITAMT] as [Debit Amount]
      ,[T1].[VOIDED] as [Voided]

FROM [ajc01].[dbo].[GL20000] T1
LEFT OUTER JOIN [ajc01].[dbo].[GL00100] T2
ON [T1].[ACTINDX] = [T2].[ACTINDX]
WHERE rtrim([T2].[ACTNUMBR_1]) + '-' + rtrim([T2].[ACTNUMBR_2]) + '-' + rtrim([T2].[ACTNUMBR_3]) LIKE ('2160-0000-C')
Order by [GL Account]

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-10-29 : 17:35:30

Remove the ORDER BY before the UNION ALL, only one ORDER BY can appear, at the very end.

msmithgp
Starting Member

2 Posts

Posted - 2011-10-29 : 17:55:15

You just saved me a lot of time.

That worked perfectly. Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources