Solved

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

Posted on 2009-05-11
6
695 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 143

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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Transact SQL - Frequency of Length of Distinct Values 3 26
Dynamic SQL select query 4 38
Where is the Help Section? 8 27
xml files 7 24
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

837 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