Solved

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

Posted on 2009-05-11
6
685 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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

707 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

16 Experts available now in Live!

Get 1:1 Help Now