Structure correct for WITH in SQL Server

Question

Structure correct for WITH in SQL Server

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

I have the following query:

;WITH TABLA1 AS (
SELECT COUNT(*) AS TOTAL_EMPLEADOS, 
TERRITORIO
FROM AR_EMPLEADOS
WHERE ANIO = 2018
AND TERRITORIO = 'A'
GROUP BY TERRITORIO
)

;WITH TABLA2 AS (
SELECT COUNT(*) AS TOTAL_PRODUCTOS,
TERRITORIO
FROM AR_PRODUCTOS
WHERE ANIO = 2018
AND TERRITORIO = 'A'
GROUP BY TERRITORIO
)

SELECT A.TERRITORIO,
A.TOTAL_EMPLEADOS,
B.AR_PRODUCTOS
FROM TABLA1 A
INNER JOIN TABLA2 B ON A.TERRITORIO = B.TERRITORIO

Error:

Incorrect syntax near ';'.

What I am trying to do if possible with the statement WITH ?

sql-server

asked by ARR 28.08.2018 в 23:50

source

2 answers

0

the correct syntax would be

WITH TABLA1 (TOTAL_EMPLEADOS, TERRITORIO) 
AS 
(
SELECT COUNT(*) AS TOTAL_EMPLEADOS, 
TERRITORIO
FROM AR_EMPLEADOS
WHERE ANIO = 2018
AND TERRITORIO = 'A'
GROUP BY TERRITORIO
)

PS: the semicolon ; is for executing several statements, if you do not have any statement before the first WITH I think it will not work for you. Put another way

[SQL Statement];

answered by 28.08.2018 в 23:56

How to add a placeholder in a JPasswordField How to tour a Collection in Laravel

score 1 · Accepted Answer

To use multiple " common table expression the appropriate syntax is:

;WITH T1 AS (...), T2 AS (..) SELECT * FROM T1 INNER JOIN T2 ON ...

For example:

;
WITH 
t1 as (select * from sysobjects),
t2 as (select * from syscolumns)

select *
    from t1
    inner join t2
        on t1.id = t2.id

And in your case:

;WITH TABLA1 AS (
SELECT COUNT(*) AS TOTAL_EMPLEADOS, 
TERRITORIO
FROM AR_EMPLEADOS
WHERE ANIO = 2018
AND TERRITORIO = 'A'
GROUP BY TERRITORIO
),
TABLA2 AS (
SELECT COUNT(*) AS TOTAL_PRODUCTOS,
TERRITORIO
FROM AR_PRODUCTOS
WHERE ANIO = 2018
AND TERRITORIO = 'A'
GROUP BY TERRITORIO
)

SELECT A.TERRITORIO,
A.TOTAL_EMPLEADOS,
B.AR_PRODUCTOS
FROM TABLA1 A
INNER JOIN TABLA2 B ON A.TERRITORIO = B.TERRITORIO