January 16, 2018 at 12:26 pm
Hello Comunity and happy new year for all of You.
I would like to know how can write this CTE using IN and Case When splitting Numeric values:
DECLARE @DataIni as Datetime
DECLARE @DataFim as Datetime
DECLARE @CBase1 as INT
DECLARE @cbase2 as INT
DECLARE @Cbase3 as INT
DECLARE @CIVA1 as INT
DECLARE @CIVA2 as INT
DECLARE @CIVA3 as INT
SET @DataIni = '20170101'
SET @DataFim = '20170131'
SET @CBase1 = 1
SET @CBase2 = 5
SET @CBase3 = 3
SET @CIVA1 = 2
SET @CIVA2 = 6
SET @CIVA3 = 4
;WITH CTE_Base
AS
( SELECT Cast(XIV.[decmes] As Varchar) [CampoDP],ml.conta,ml.descricao ,
ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno,SUM([edeb]) [Debito],SUM(ecre)[Credito],XIV.Tipo [TipoSaldo_DP]
FROM
(SELECT [cod],decmes, Tipo FROM iv) XIV
LEFT JOIN [dbo].[pc] ON [XIV].[cod] = [dbo].[pc].[codiva]
LEFT JOIN [dbo].[ml] ON ml.[conta] = [dbo].[pc].conta
LEFT JOIN do ON ml.dostamp = do.dostamp
WHERE
[dbo].[pc].[ano] = Year(@DataFim)
AND [dbo].[ml].[data] BETWEEN @DataIni AND @DataFim
AND [XIV].[decmes] IN
(CASE WHEN 0 = 0 THEN
( @CBase1, @CBase2, @CBase3) ELSE 24 END )
GROUP BY
XIV.[decmes], ml.conta,ml.descricao,XIV.Tipo,ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno )
SELECT * FROM CTE_Base
Many Thanks,
Luis
January 16, 2018 at 12:39 pm
You will need to use dynamic SQL.
DECLARE @sql nvarchar(4000);
SET @sql =
'WITH CTE_Base
AS
( SELECT Cast(XIV.[decmes] As Varchar) [CampoDP],ml.conta,ml.descricao ,
ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno,SUM([edeb]) [Debito],SUM(ecre)[Credito],XIV.Tipo [TipoSaldo_DP]
FROM
(SELECT [cod],decmes, Tipo FROM iv) XIV
LEFT JOIN [dbo].[pc] ON [XIV].[cod] = [dbo].[pc].[codiva]
LEFT JOIN [dbo].[ml] ON ml.[conta] = [dbo].[pc].conta
LEFT JOIN do ON ml.dostamp = do.dostamp
WHERE
[dbo].[pc].[ano] = Year('''+@DataFim+''')
AND [dbo].[ml].[data] BETWEEN '''+@DataIni+''' AND '''+@DataFim+'''
AND [XIV].[decmes] IN( '''+@CBase1+''', '''+@CBase2+''', '''+@CBase3+''')
GROUP BY
XIV.[decmes], ml.conta,ml.descricao,XIV.Tipo,ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno)
SELECT * FROM CTE_Base';
EXEC (@sql);
January 16, 2018 at 1:18 pm
Joe Torre - Tuesday, January 16, 2018 12:39 PMYou will need to use dynamic SQL.
DECLARE @sql nvarchar(4000);
SET @sql =
'WITH CTE_Base
AS
( SELECT Cast(XIV.[decmes] As Varchar) [CampoDP],ml.conta,ml.descricao ,
ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno,SUM([edeb]) [Debito],SUM(ecre)[Credito],XIV.Tipo [TipoSaldo_DP]
FROM
(SELECT [cod],decmes, Tipo FROM iv) XIV
LEFT JOIN [dbo].[pc] ON [XIV].[cod] = [dbo].[pc].[codiva]
LEFT JOIN [dbo].[ml] ON ml.[conta] = [dbo].[pc].conta
LEFT JOIN do ON ml.dostamp = do.dostamp
WHERE
[dbo].[pc].[ano] = Year('''+@DataFim+''')
AND [dbo].[ml].[data] BETWEEN '''+@DataIni+''' AND '''+@DataFim+'''
AND [XIV].[decmes] IN( '''+@CBase1+''', '''+@CBase2+''', '''+@CBase3+''')
GROUP BY
XIV.[decmes], ml.conta,ml.descricao,XIV.Tipo,ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno)
SELECT * FROM CTE_Base';
EXEC (@sql);
There is absolutely no reason for this to be dynamic SQL based on what we have been given. Also, this dynamic SQL is vulnerable to SQL injection. Do not use this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 16, 2018 at 1:20 pm
luissantos - Tuesday, January 16, 2018 12:26 PMHello Comunity and happy new year for all of You.I would like to know how can write this CTE using IN and Case When splitting Numeric values:
DECLARE @DataIni as Datetime
DECLARE @DataFim as Datetime
DECLARE @CBase1 as INT
DECLARE @cbase2 as INT
DECLARE @Cbase3 as INTDECLARE @CIVA1 as INT
DECLARE @CIVA2 as INT
DECLARE @CIVA3 as INTSET @DataIni = '20170101'
SET @DataFim = '20170131'
SET @CBase1 = 1
SET @CBase2 = 5
SET @CBase3 = 3SET @CIVA1 = 2
SET @CIVA2 = 6
SET @CIVA3 = 4;WITH CTE_Base
AS
( SELECT Cast(XIV.[decmes] As Varchar) [CampoDP],ml.conta,ml.descricao ,
ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno,SUM([edeb]) [Debito],SUM(ecre)[Credito],XIV.Tipo [TipoSaldo_DP]
FROM
(SELECT [cod],decmes, Tipo FROM iv) XIV
LEFT JOIN [dbo].[pc] ON [XIV].[cod] = [dbo].[pc].[codiva]
LEFT JOIN [dbo].[ml] ON ml.[conta] = [dbo].[pc].conta
LEFT JOIN do ON ml.dostamp = do.dostamp
WHERE
[dbo].[pc].[ano] = Year(@DataFim)
AND [dbo].[ml].[data] BETWEEN @DataIni AND @DataFim
AND [XIV].[decmes] IN
(CASE WHEN 0 = 0 THEN
( @CBase1, @CBase2, @CBase3) ELSE 24 END )
GROUP BY
XIV.[decmes], ml.conta,ml.descricao,XIV.Tipo,ml.ncont,ml.PNCONT,ml.dostamp,ml.dinome,ml.dilno )SELECT * FROM CTE_Base
Many Thanks,
Luis
There is no need for the CASE expression as written. Furthermore, you're trying to return multiple values in your CASE expression, but you can only return a single value. Change the criteria to AND [XIV].[decmes] IN
( @CBase1, @CBase2, @CBase3)
I also don't see anywhere that you are splitting Numeric values.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 16, 2018 at 1:35 pm
Why do people insist on using CASE on WHERE clauses. That's usually a bad idea as it would prevent any index from being used.
Here's a possible alternative.
WHERE [dbo].[pc].[ano] = Year(@DataFim)
AND [dbo].[ml].[data] BETWEEN @DataIni AND @DataFim
AND (( 0 = 0 AND [XIV].[decmes] IN ( @CBase1, @CBase2, @CBase3))
OR ( 0 <> 0 AND [XIV].[decmes] = 24 ))
I'm assuming that the 0=0 is some other condition that will be changed.
January 16, 2018 at 1:56 pm
Thank you all for the help.
Best regrards,
Luis
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply