UNION ALL FOR 2 CTEs - 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

UNION ALL FOR 2 CTEs

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-02-07 : 16:51:14

I have two scripts that alone work like a charm (with the help of people from this forum)

One is:

WITH query1 AS (SELECT vo_id, voline,
STUFF((SELECT ' ' + des FROM son_db.dbo.apvodes WHERE vo_id = t.vo_id AND voline = t.voline ORDER BY dsline FOR XML PATH('')),1,1,'') AS [des]
FROM (SELECT DISTINCT vo_id,voline FROM son_db.dbo.apvodes)t)

SELECT csddt.ckdate AS Date, csddt.cknum AS 'Check #/GJ ID', NULL AS 'GL#',csddt.ckline AS Line, csddt.apnum As 'Vendor#', ap.apname As 'Vendor Name', apaddr.appynm As Payee, csddt.glnum As 'GL Acct', gl.seg1 AS 'Segment 1', gl.seg2 AS 'Segment 2', gl.seg3 AS 'Segment 3', gl.seg4 AS 'Segment 4', gl.seg5 AS 'Segment 5', gl.gldes As 'GL Description', csd.voiddt As 'Void Date', csddt.currency As Currency, csddt.vo_id As 'Voucher ID',
CASE when csddt.amt > 0
then csddt.amt
else 0 end AS Debit,
CASE when csddt.amt < 0
then csddt.amt * -1
else 0 end AS Credit,
query1.des AS 'Description'

FROM
( ((
son_db.dbo.csddt csddt
INNER JOIN son_db.dbo.csd csd ON
(csddt.cknum=csd.cknum)
AND (csddt.baid=csd.baid)
)INNER JOIN son_db.dbo.apaddr apaddr ON
(csddt.apnum=apaddr.apnum)
AND (csddt.apaddid=apaddr.apaddid)) INNER JOIN son_db.dbo.gl gl ON
(csddt.glnum=gl.glnum)
AND (csddt.glrc1=gl.glrc1))
LEFT OUTER JOIN son_db.dbo.ap ap ON
csddt.apnum=ap.apnum
INNER JOIN query1 ON (csddt.vo_id = query1.vo_id AND csddt.ckline = query1.voline)

the other is:

WITH query1 AS (SELECT gjid, gjline,
STUFF((SELECT ' ' + des FROM son_db.dbo.gjdes WHERE gjid = t.gjid AND gjline = t.gjline ORDER BY gjline FOR XML PATH('')),1,1,'') AS [des]
FROM (SELECT DISTINCT gjid,gjline FROM son_db.dbo.gjdes)t)

SELECT gj.gjtrdt AS DATE, gj.gjid AS 'Check #/GJ ID', gj.gjbtid AS 'GL#', gj.gjline AS Line, NULL As 'Vendor#', NULL As 'Vendor Name', NULL As Payee, gj.glnum As 'GL Acct', gl.seg1 AS 'Segment 1', gl.seg2 AS 'Segment 2', gl.seg3 AS 'Segment 3', gl.seg4 AS 'Segment 4', gl.seg5 AS 'Segment 5', gl.gldes As 'GL Description', NULL As 'Void Date', gj.currency As Currency, NULL As 'Voucher ID', gj.dbamt AS Debit, gj.cramt AS Credit,query1.des AS 'Description'

FROM son_db.dbo.gj gj INNER JOIN son_db.dbo.gl gl ON (gj.glnum=gl.glnum) AND (gj.glrc1=gl.glrc1) INNER JOIN query1 ON (gj.gjid = query1.gjid AND gj.gjline = query1.gjline)
WHERE gj.gltype<>'A'

I want to combine the data of those two scripts into one so I though of Union ALL

I used:

WITH query1 AS (SELECT vo_id, voline,
STUFF((SELECT ' ' + des FROM son_db.dbo.apvodes WHERE vo_id = t.vo_id AND voline = t.voline ORDER BY dsline FOR XML PATH('')),1,1,'') AS [des]
FROM (SELECT DISTINCT vo_id,voline FROM son_db.dbo.apvodes)t)

SELECT csddt.ckdate AS Date, csddt.cknum AS 'Check #/GJ ID', NULL AS 'GL#',csddt.ckline AS Line, csddt.apnum As 'Vendor#', ap.apname As 'Vendor Name', apaddr.appynm As Payee, csddt.glnum As 'GL Acct', gl.seg1 AS 'Segment 1', gl.seg2 AS 'Segment 2', gl.seg3 AS 'Segment 3', gl.seg4 AS 'Segment 4', gl.seg5 AS 'Segment 5', gl.gldes As 'GL Description', csd.voiddt As 'Void Date', csddt.currency As Currency, csddt.vo_id As 'Voucher ID',
CASE when csddt.amt > 0
then csddt.amt
else 0 end AS Debit,
CASE when csddt.amt < 0
then csddt.amt * -1
else 0 end AS Credit,
query1.des AS 'Description'

FROM
(
(
(
son_db.dbo.csddt csddt
INNER JOIN son_db.dbo.csd csd ON
(csddt.cknum=csd.cknum)
AND (csddt.baid=csd.baid)
)
INNER JOIN son_db.dbo.apaddr apaddr ON
(csddt.apnum=apaddr.apnum)
AND (csddt.apaddid=apaddr.apaddid)
)
INNER JOIN son_db.dbo.gl gl ON
(csddt.glnum=gl.glnum)
AND (csddt.glrc1=gl.glrc1)
)
LEFT OUTER JOIN son_db.dbo.ap ap ON
csddt.apnum=ap.apnum

INNER JOIN query1 ON (csddt.vo_id = query1.vo_id AND csddt.ckline = query1.voline)

UNION ALL

WITH query1 AS (SELECT gjid, gjline,
STUFF((SELECT ' ' + des FROM son_db.dbo.gjdes WHERE gjid = t.gjid AND gjline = t.gjline ORDER BY gjline FOR XML PATH('')),1,1,'') AS [des]
FROM (SELECT DISTINCT gjid,gjline FROM son_db.dbo.gjdes)t)

SELECT gj.gjtrdt AS DATE, gj.gjid AS 'Check #/GJ ID', gj.gjbtid AS 'GL#', gj.gjline AS Line, NULL As 'Vendor#', NULL As 'Vendor Name', NULL As Payee, gj.glnum As 'GL Acct', gl.seg1 AS 'Segment 1', gl.seg2 AS 'Segment 2', gl.seg3 AS 'Segment 3', gl.seg4 AS 'Segment 4', gl.seg5 AS 'Segment 5', gl.gldes As 'GL Description', NULL As 'Void Date', gj.currency As Currency, NULL As 'Voucher ID', gj.dbamt AS Debit, gj.cramt AS Credit,query1.des AS 'Description'

FROM son_db.dbo.gj gj INNER JOIN son_db.dbo.gl gl ON (gj.glnum=gl.glnum) AND (gj.glrc1=gl.glrc1) INNER JOIN query1 ON (gj.gjid = query1.gjid AND gj.gjline = query1.gjline)
WHERE gj.gltype<>'A'

But it gives me the following error:
Incorrect syntas near keyword "WITH", .... If this statement is a common table expression, an xmlnamespaces clause ....., the previous statement must be terminated with a semicolon.

I tried to place semicolons before and after the WITH keywords but doesn't seem to work. Any ideas?

Thanks for the help.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-07 : 16:54:40

Well you're naming the CTE the exact same CTE Name (Query1)

However, I don't think that's the problem

You COULD however do this

WITH Query1...

SELECT * INTO #Query1 FROM Query1

WITH Query2...

SELECT * INTO #Query2 FROM Query2

And then just use the Temp Tables

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources