I would like to use WITH before the query but I skip error

Question

I would like to use WITH before the query but I skip error

Navigation

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

0

Well, I want to use WITH before the query but I miss an error, I attach the query of how I put the WITH and the error.

DECLARE @cod_centro VARCHAR(2)
DECLARE @fec_annomes int

SET @cod_centro='MC'
SET @fec_annomes='201810'


WITH TH_Hospitalizacion AS

(
select @cod_centro, @fec_annomes, 225,count(* ),getdate()
from TH_Hospitalizacion t 
where t.cod_centro = @cod_centro and year(t.fec_alta)*100+month(t.fec_alta) = @fec_annomes
and xti_excluido ='n' and xti_tipoProceso='H'
AND EXISTS (SELECT COD_EPISODIO FROM TH_TomaFormulario A WITH (NOLOCK)
INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) ON A.cod_TomaFormulario = B.cod_TomaFormulario 
AND B.COD_INDICADOR IN ('5913',
'10965',
'48128')
and des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10')
AND t.cod_Episodio=A.cod_Episodio
AND A.cod_centro=@cod_centro)

)

This is the error message.

Sintaxis incorrecta junto a la palabra clave 'with'. Si esta instrucción es una expresión de tabla común, una cláusula xmlnamespaces o una cláusula de contexto de seguimiento de cambios, la instrucción anterior debe terminarse con punto y coma.

If someone knows how to solve it, I would appreciate it a lot.

Greetings and thanks in advance.

sql

asked by Borja Moreno 30.10.2018 в 16:45

source

2 answers

0

The TSQL syntax, requires that, if there are statements in the script prior to with , the last of these always ends with ; . The error is that in your original script that ; does not exist.

Since it is the only case (that I know of) that TSQL has that rule, it is a common practice to always start the CTE with ; .

For example, in your case, it will be resolved in either of these two ways:

SET @fec_annomes='201810'; --; añadido aquí


WITH TH_Hospitalizacion AS

(
select @cod_centro, @fec_annomes, 225,count(* ),getdate()
from TH_Hospitalizacion t 
... el resto de tu código

But also like that (and it's the favorite of many):

SET @fec_annomes='201810'


; WITH TH_Hospitalizacion AS --al añadirlo aquí, siempre se cumple la regla

(
select @cod_centro, @fec_annomes, 225,count(* ),getdate()
from TH_Hospitalizacion t

answered by 30.10.2018 в 17:27

how to pass multiple values in a single parameter in stored procedure oracle plsql Problem when setting rps Jmeter

score 0 · Accepted Answer

If what you want to do is a CTE, in the final part you were missing the invocation to the generated CTE.

WITH TH_Hospitalizacion_CTE AS
(
    SELECT @cod_centro AS 'Cod_Centro', 
        @fec_annomes AS 'Fecha', 
        225 AS '225',
        count(* ) AS 'Count',
        getdate() AS 'GetDate'
    FROM TH_Hospitalizacion t 
    WHERE t.cod_centro = @cod_centro 
        AND year(t.fec_alta)*100+month(t.fec_alta) = @fec_annomes
        AND xti_excluido ='n' and xti_tipoProceso='H'
        AND EXISTS (SELECT COD_EPISODIO 
            FROM TH_TomaFormulario A WITH (NOLOCK)
            INNER JOIN TH_ValorIndicadorTomaFormulario_2 B WITH (NOLOCK) 
                ON A.cod_TomaFormulario = B.cod_TomaFormulario 
                AND B.COD_INDICADOR IN ('5913', '10965', '48128')
                AND des_ValorIndicador IN ('0','1','2','3','4','5','6','7','8','9', '10')
                AND A.cod_centro=@cod_centro
            WHERE t.cod_Episodio=A.cod_Episodio
            )
)
SELECT *
FROM TH_Hospitalizacion_CTE

Two things worth mentioning:

Avoid calling the same CTE with a name already assigned within your tables, that's why I changed it to TH_Hospitalizacion_CTE.

In the ON do not filter out that they do not have to do with the relation of tables by the JOINS , in that case what you must do is the use of the > WHERE .

EDITING

What is missing is that you give an alias to the calculated fields. Greetings.