Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 700
  • Last Modified:

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

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
megnin
Asked:
megnin
4 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
HwkrangerCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
tigin44Commented:
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
 
megninAuthor Commented:
Great!  Thank you.  I learned something new today.  :-)
0
 
megninAuthor Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now