Solved

"; WITH clause after UNION causes "Incorrect syntax near ";"

Posted on 2009-05-11
6
688 Views
Last Modified: 2012-06-27
The SQL statement above the first UNION goes fine.  All the part below the first UNION goes fine with or without the ";", but when I put them together with or without the ";" I get either:
Msg 102, Level 15, State 1, Line 114
Incorrect syntax near ';'.
or
Msg 156, Level 15, State 1, Line 114
Incorrect syntax near the keyword 'WITH'.

...depending on whether the ";" is in or out.  (right after the comment)
(SELECT x1.measure,

        x1.tally1 + x2.tally2 AS tally

 FROM   (SELECT '05) Total CSC positions created' AS measure,

                Sum(positionsavailable)           AS tally1

         FROM   jobtitles

         WHERE  (keyemployerid > 200900000)

                AND (keyemployerid < 200910201)

                AND employername NOT LIKE '%xxx%'

                AND (employername NOT LIKE '%xx NONE xx%')

                AND (employername NOT LIKE '%zz NONE zz%')

                AND positionsavailable IS NOT NULL) x1,

        (SELECT '05) Total CSC positions created'       AS measure,

                Sum(txtworksitejobtitlepositionsnumber) AS tally2

         FROM   jobtitles

         WHERE  (keyemployerid > 200900000)

                AND (keyemployerid < 200910201)

                AND employername NOT LIKE '%xxx%'

                AND (employername NOT LIKE '%xx NONE xx%')

                AND (employername NOT LIKE '%zz NONE zz%')

                AND positionsavailable IS NULL) x2

 WHERE  x1.measure = x2.measure)

UNION 

-- From ralmada at Experts-Exchange

;WITH grandtotal

     AS ((SELECT x1.measure,

                 x1.tally1 + x2.tally2 AS tally,

                 x1.tally1 + x2.tally2 AS temp_tally

          FROM   (SELECT '14) Total WIA positions created' AS measure,

                         Sum(positionsavailable)           AS tally1

                  FROM   jobtitles

                  WHERE  (keyemployerid > 200910201)

						 AND employername NOT LIKE '%xxx%'

				         AND (employername NOT LIKE '%xx NONE xx%')

						 AND (employername NOT LIKE '%zz NONE zz%')

                         AND positionsavailable IS NOT NULL) x1,

                 (SELECT '14) Total WIA positions created'       AS measure,

                         Sum(txtworksitejobtitlepositionsnumber) AS tally2

                  FROM   jobtitles

                  WHERE  (keyemployerid > 200910201)

		                 AND employername NOT LIKE '%xxx%'

				         AND (employername NOT LIKE '%xx NONE xx%')

						 AND (employername NOT LIKE '%zz NONE zz%')

                         AND positionsavailable IS NULL) x2

          WHERE  x1.measure = x2.measure)

         UNION 

         SELECT '15) WIA Positions remaining to be filled' AS measure,

                Sum(txtworksitejobtitlepositionsnumber)    AS tally,

                -Sum(txtworksitejobtitlepositionsnumber)   AS temp_tally

         FROM   jobtitles

         WHERE  (keyemployerid > 200910201)

				        AND employername NOT LIKE '%xxx%'

						AND (employername NOT LIKE '%xx NONE xx%')

         )

SELECT measure,

       tally

FROM   grandtotal

UNION 

SELECT '16) Positions filled' AS measure,

       Sum(temp_tally)

FROM   grandtotal

Open in new window

0
Comment
Question by:megnin
6 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 24359102
you have to use the WITH before anything else:
;WITH grandtotal
     AS ((SELECT x1.measure,
                 x1.tally1 + x2.tally2 AS tally,
                 x1.tally1 + x2.tally2 AS temp_tally
          FROM   (SELECT '14) Total WIA positions created' AS measure,
                         Sum(positionsavailable)           AS tally1
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
                                     AND employername NOT LIKE '%xxx%'
                                 AND (employername NOT LIKE '%xx NONE xx%')
                                     AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NOT NULL) x1,
                 (SELECT '14) Total WIA positions created'       AS measure,
                         Sum(txtworksitejobtitlepositionsnumber) AS tally2
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
                             AND employername NOT LIKE '%xxx%'
                                 AND (employername NOT LIKE '%xx NONE xx%')
                                     AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NULL) x2
          WHERE  x1.measure = x2.measure)
         UNION 
         SELECT '15) WIA Positions remaining to be filled' AS measure,
                Sum(txtworksitejobtitlepositionsnumber)    AS tally,
                -Sum(txtworksitejobtitlepositionsnumber)   AS temp_tally
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                                AND employername NOT LIKE '%xxx%'
                                    AND (employername NOT LIKE '%xx NONE xx%')
         )
SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 FROM   (SELECT '05) Total CSC positions created' AS measure,
                Sum(positionsavailable)           AS tally1
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '05) Total CSC positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure
UNION ALL
SELECT measure,
       tally
FROM   grandtotal
UNION ALL
SELECT '16) Positions filled' AS measure,
       Sum(temp_tally)
FROM   grandtotal

Open in new window

0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 50 total points
ID: 24359108
replace

-- From ralmada at Experts-Exchange
;WITH grandtotal
     AS ((SELECT x1.measure,
                 x1.tally1 + x2.tally2 AS tally,
                 x1.tally1 + x2.tally2 AS temp_tally
          FROM   (SELECT '14) Total WIA positions created' AS measure,
                         Sum(positionsavailable)           AS tally1
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
                                     AND employername NOT LIKE '%xxx%'
                                 AND (employername NOT LIKE '%xx NONE xx%')
                                     AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NOT NULL) x1,
                 (SELECT '14) Total WIA positions created'       AS measure,
                         Sum(txtworksitejobtitlepositionsnumber) AS tally2
                  FROM   jobtitles
                  WHERE  (keyemployerid > 200910201)
                             AND employername NOT LIKE '%xxx%'
                                 AND (employername NOT LIKE '%xx NONE xx%')
                                     AND (employername NOT LIKE '%zz NONE zz%')
                         AND positionsavailable IS NULL) x2
          WHERE  x1.measure = x2.measure)
         UNION 
         SELECT '15) WIA Positions remaining to be filled' AS measure,
                Sum(txtworksitejobtitlepositionsnumber)    AS tally,
                -Sum(txtworksitejobtitlepositionsnumber)   AS temp_tally
         FROM   jobtitles
         WHERE  (keyemployerid > 200910201)
                                AND employername NOT LIKE '%xxx%'
                                    AND (employername NOT LIKE '%xx NONE xx%')
         )
SELECT measure,
       tally
FROM   grandtotal
UNION 
SELECT '16) Positions filled' AS measure,
       Sum(temp_tally)
FROM   grandtotal
UNION  
(SELECT x1.measure,
        x1.tally1 + x2.tally2 AS tally
 FROM   (SELECT '05) Total CSC positions created' AS measure,
                Sum(positionsavailable)           AS tally1
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NOT NULL) x1,
        (SELECT '05) Total CSC positions created'       AS measure,
                Sum(txtworksitejobtitlepositionsnumber) AS tally2
         FROM   jobtitles
         WHERE  (keyemployerid > 200900000)
                AND (keyemployerid < 200910201)
                AND employername NOT LIKE '%xxx%'
                AND (employername NOT LIKE '%xx NONE xx%')
                AND (employername NOT LIKE '%zz NONE zz%')
                AND positionsavailable IS NULL) x2
 WHERE  x1.measure = x2.measure)
 

Open in new window

0
 
LVL 9

Assisted Solution

by:Hwkranger
Hwkranger earned 100 total points
ID: 24359116
You're trying to Union then declaring a CTE, then trying ot union the CTE with another set.

You should put all the data in your CTE (That's the with statement) then try to union it with your second data set.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 26

Assisted Solution

by:tigin44
tigin44 earned 100 total points
ID: 24359165
use with like the one below before nay expression
-- From ralmada at Experts-Exchange

WITH grandtotal

     AS ((SELECT x1.measure,

                 x1.tally1 + x2.tally2 AS tally,

                 x1.tally1 + x2.tally2 AS temp_tally

          FROM   (SELECT '14) Total WIA positions created' AS measure,

                         Sum(positionsavailable)           AS tally1

                  FROM   jobtitles

                  WHERE  (keyemployerid > 200910201)

                                                 AND employername NOT LIKE '%xxx%'

                                         AND (employername NOT LIKE '%xx NONE xx%')

                                                 AND (employername NOT LIKE '%zz NONE zz%')

                         AND positionsavailable IS NOT NULL) x1,

                 (SELECT '14) Total WIA positions created'       AS measure,

                         Sum(txtworksitejobtitlepositionsnumber) AS tally2

                  FROM   jobtitles

                  WHERE  (keyemployerid > 200910201)

                                 AND employername NOT LIKE '%xxx%'

                                         AND (employername NOT LIKE '%xx NONE xx%')

                                                 AND (employername NOT LIKE '%zz NONE zz%')

                         AND positionsavailable IS NULL) x2

          WHERE  x1.measure = x2.measure)

         UNION 

         SELECT '15) WIA Positions remaining to be filled' AS measure,

                Sum(txtworksitejobtitlepositionsnumber)    AS tally,

                -Sum(txtworksitejobtitlepositionsnumber)   AS temp_tally

         FROM   jobtitles

         WHERE  (keyemployerid > 200910201)

                                        AND employername NOT LIKE '%xxx%'

                                                AND (employername NOT LIKE '%xx NONE xx%')

         )

SELECT measure,

       tally

FROM   grandtotal

UNION 

SELECT '16) Positions filled' AS measure,

       Sum(temp_tally)

FROM   grandtotal
 

UNION
 

(SELECT x1.measure,

        x1.tally1 + x2.tally2 AS tally

 FROM   (SELECT '05) Total CSC positions created' AS measure,

                Sum(positionsavailable)           AS tally1

         FROM   jobtitles

         WHERE  (keyemployerid > 200900000)

                AND (keyemployerid < 200910201)

                AND employername NOT LIKE '%xxx%'

                AND (employername NOT LIKE '%xx NONE xx%')

                AND (employername NOT LIKE '%zz NONE zz%')

                AND positionsavailable IS NOT NULL) x1,

        (SELECT '05) Total CSC positions created'       AS measure,

                Sum(txtworksitejobtitlepositionsnumber) AS tally2

         FROM   jobtitles

         WHERE  (keyemployerid > 200900000)

                AND (keyemployerid < 200910201)

                AND employername NOT LIKE '%xxx%'

                AND (employername NOT LIKE '%xx NONE xx%')

                AND (employername NOT LIKE '%zz NONE zz%')

                AND positionsavailable IS NULL) x2

 WHERE  x1.measure = x2.measure)

Open in new window

0
 
LVL 1

Author Closing Comment

by:megnin
ID: 31580331
Great!  Thank you.  I learned something new today.  :-)
0
 
LVL 1

Author Comment

by:megnin
ID: 24359196
Thank you very much!  W3Shools.com didn't even mention that "WITH" had to come before anything else.  I just moved the whole last section to the very top and it works fine.
Thank you again, especially for the quick answers!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL FUll Text Not returning any results 2 15
mysql joining from the same table 6 32
SQL Select Statement 2 21
Getting max record but maybe not use Group BY 2 15
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now