Help with UNION ALL statement - 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

Help with UNION ALL statement

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-05-02 : 11:35:43

Hi,
I have two pieces of code which separetaly work great. I'm trying to combine them with a UNION ALL statement, which I've done before with good results, the problem here is that there is a "WITH" statement present, I get an error saying that apparently I should enter a semicolon somewhere, but not sure where. Any help will abe appreciated.
Thank you!

WITH X AS(
SELECT tindex,
STUFF((SELECT ' ' + tddesc FROM son_db.dbo.timedesc WHERE tindex = t.tindex ORDER BY tdline FOR XML PATH('')),1,1,'') AS [tddesc]

FROM (SELECT DISTINCT tindex FROM son_db.dbo.timedesc)t
)
SELECT
client.clnum AS 'Client'
, matter.mmatter AS 'Matter'
, ISNULL(matter.mdesc1,' ') +' '+ISNULL(matter.mdesc2,' ')+' '+ISNULL(matter.mdesc3,' ') AS 'Description'
, timecard.ttk AS 'TTKID'
, timekeep.tklast + ', ' + timekeep.tkfirst AS 'Timekeeper'
, timekeep.tktitle AS 'Title'
, x.tindex AS 'Index'
, timecard.tworkdt AS 'WorkedDate'
, timecard.tworkhrs AS 'Hours'
, timecard.tworkdol AS 'WorkedAmount'
, timecard.tbilldt AS 'BilledDate'
, timecard.tbillhrs AS 'BilledHours'
, timecard.tbilldol AS 'BilledAmount'
, timecard.tstatus AS 'Status'
, timecard.tinvoice AS 'Invoice'
, matter.mcurrency AS 'Currency'
, x.tddesc AS 'Diary'

FROM
X
, son_db.dbo.client client
, son_db.dbo.timecard timecard
, son_db.dbo.matter matter
, son_db.dbo.timekeep timekeep

WHERE X.TINDEX = timecard.tindex
AND matter.mmatter = timecard.tmatter
AND client.clnum = matter.mclient
AND timecard.ttk = timekeep.tkinit
AND (timecard.tprntfl Not In ('R','X'))
AND (timecard.tstatus<>'ADE, D, E, NB, NBP')
AND (matter.mmatter Like 'BCCI-652-001')
AND (timecard.ttk Like '%')
AND (timecard.tworkdt>={ts '2012-01-01 00:00:00'})
AND (timecard.tworkdt<={ts '2012-01-31 00:00:00'})

UNION ALL

WITH X AS(
SELECT cindex,
STUFF((SELECT ' ' + cddesc FROM son_db.dbo.costdesc WHERE cindex = t.cindex ORDER BY cdline FOR XML PATH('')),1,1,'') AS [cddesc]

FROM (SELECT DISTINCT cindex FROM son_db.dbo.costdesc)t
)
SELECT
client.clnum AS 'Client'
, matter.mmatter AS 'Matter'
, ISNULL(matter.mdesc1,' ') +' '+ISNULL(matter.mdesc2,' ')+' '+ISNULL(matter.mdesc3,' ') AS 'Description'
, cost.ctk AS 'TTKID'
, timekeep.tklast + ', ' + timekeep.tkfirst AS 'Timekeeper'
, timekeep.tktitle AS 'Title'
, x.cindex AS 'Index'
, cost.cdisbdt AS 'WorkedDate'
, cost.cquant AS 'Hours'
, cost.camount AS 'WorkedAmount'
, cost.cbilldt AS 'BilledDate'
, cost.cquant AS 'BilledHours'
, cost.cbillamt AS 'BilledAmount'
, cost.cstatus AS 'Status'
, cost.cinvoice AS 'Invoice'
, matter.mcurrency AS 'Currency'
, x.cddesc AS 'Diary'

FROM
X
, son_db.dbo.client client
, son_db.dbo.cost cost
, son_db.dbo.matter matter
, son_db.dbo.timekeep timekeep

WHERE X.cINDEX = cost.cindex
AND matter.mmatter = cost.cmatter
AND client.clnum = matter.mclient
AND cost.ctk = timekeep.tkinit
AND (cost.cstatus<>'ADE, D, E, NB, NBP')
AND (matter.mmatter Like 'BCCI-652-001')
AND (cost.ctk Like '%')
AND (cost.cdisbdt>={ts '2012-01-01 00:00:00'})
AND (cost.cdisbdt<={ts '2012-01-31 00:00:00'})

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-05-02 : 11:47:58

Try terminiating the last statement before the WITH expression with a semicolon (or just put a semicolon right before the WITH).

As en example, if you remove the semicolon below it will generate an error:

SELECT 1;-- Remove Semicolon for error

WITH X AS (SELECT 1 AS Foo)

SELECT *
FROM X

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-02 : 12:18:30

(

This is CTE (common Table Expression)

start this with Semicolon like below.

;WITH X AS

jaimealvarez
Starting Member

31 Posts

Posted - 2012-05-02 : 13:30:00

Thank you guys, your comments helped me solve this one.

First of all you were right with the semicolon at the beginning.

Second, and this is just in case anyone has the same problem out there:
I had to CTEs and was placing one of them after the UNION ALL statement, this was wrong they have to be both declare at the begining using the syntax that is mentioned in the above comments.

Thank you again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources