SQL ANSI_NULLS Warnings are missunderstood like an error in turbointegrator
Posted: Wed Jan 25, 2023 4:30 pm
Hi,
I have a TI with ODBC as Data Source and I am using an SQL query previously tested in SQL Management Studio and directly in ZEUS (Accounting software) by IT team but "sometimes" the process fails and returns this message:
The first solution is to set ansi_warnings OFF in the execution time.This is possible with just execute this instruction:
This is the query working:
This is the error in log when the case happen:
This is the error when I try to fix it adding the instruction:
This is the SQL statement:
It seems like I am answering the question by myself but, I really have some limitations: IT team confirmed that we can´t create Stored Procedures in this accounting software.
So, I am asking you if maybe you can show me another solution:
Do you know another way to avoid this warning when I run the TI ? OR
Do you know any way to say TM1 doesn't read this warning like an error?
Thank you very much for read and help.
I have a TI with ODBC as Data Source and I am using an SQL query previously tested in SQL Management Studio and directly in ZEUS (Accounting software) by IT team but "sometimes" the process fails and returns this message:
After read about this error I understood this is only a Warning (triggered when Aggregate Function ignores a null. See an example here:https://blog.sqlauthority.com/2015/02/1 ... operation/) value instead of Error.Error: Data procedure line (0): SQLState: 01003 SQLMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]Warning: Null value is eliminated by an aggregate or other SET operation.
The first solution is to set ansi_warnings OFF in the execution time.This is possible with just execute this instruction:
BUT, when I Put this line before the SQL Query it returns a sintax error... I think Turbointegrator only can process SQL Queries or Call Stored Procedures but not execute this kind of instruction...SET ANSI_WARNINGS OFF
This is the query working:
This is the error in log when the case happen:
This is the error when I try to fix it adding the instruction:
This is the SQL statement:
Code: Select all
SET ANSI_WARNINGS OFF
GO
WITH MaeCuentas AS
(select H1.CODICTA [Cuenta], H1.CATFCTA, H1.DESCCTA [Descripcion]
from [scio-sql-zeus-sprc,1440].[Contabilidad_SPRC].dbo.MAECONT as H1
where TIPOCTA='D'
and (h1.codicta between '10000000000000' and '99999999999999')
and h1.codicta not in ('936050501000001','959050500010101')),
ListaCuentas as
(Select distinct H4.CODICTA [ListaCuenta]
FROM [scio-sql-zeus-sprc,1440].[Contabilidad_SPRC].dbo.SALDOCONT_BU as H4
where (H4.anomescta BETWEEN '202211' AND '202211')),
Cuentas as
(select H3.cuenta, H3.descripcion,H3.CATFCTA
from MaeCuentas as H3
join ListaCuentas on ListaCuentas.ListaCuenta = H3.cuenta),
sldIniLocal As
(Select SALDOCONT_BU.CODICTA [CuentaLocal],
SALDOCONT_BU.SDANCTA [SaldoAnt_L], SALDOCONT_BU.MVDBCTA*0 [MvDebito_L], SALDOCONT_BU.MVCRCTA*0 [MvCredito_L],
SALDOCONT_BU.SDACCTA*0 [SaldoActual_L]
FROM [scio-sql-zeus-sprc,1440].[Contabilidad_SPRC].dbo.SALDOCONT_BU
where saldocont_bu.anomescta = '202211'
and SALDOCONT_BU.BU = 'N-Local' ),
MvLocal AS
(Select SALDOCONT_BU.CODICTA [CuentaLocal],
0 [SaldoAnt_L], sum(SALDOCONT_BU.MVDBCTA) [MvDebito_L], sum(SALDOCONT_BU.MVCRCTA) [MvCredito_L],
0 [SaldoActual_L]
FROM [scio-sql-zeus-sprc,1440].[Contabilidad_SPRC].dbo.SALDOCONT_BU
where (saldocont_bu.anomescta BETWEEN '202211' AND '202211')
and right(SALDOCONT_BU.anomescta,1) <> 'C'
and SALDOCONT_BU.BU = 'N-Local'
group by codicta),
sldFinLocal as
(Select SALDOCONT_BU.CODICTA [CuentaLocal],
0 [SaldoAnt_L], 0 [MvDebito_L], 0 [MvCredito_L],
SALDOCONT_BU.SDACCTA [SaldoActual_L], SALDOCONT_BU.SDACCTA_T [SDACCTA_T_L]
FROM [scio-sql-zeus-sprc,1440].[Contabilidad_SPRC].dbo.SALDOCONT_BU
where saldocont_bu.anomescta = '202211'
and SALDOCONT_BU.BU = 'N-Local' ),
AnexoTemp as
(select R0.Cuenta, R0.Descripcion,
0 [saldoAnt_L], 0 [MvDebito_L], 0 [MvCredito_L], 0 [SaldoActual_L]
from cuentas as R0
union
select R1.Cuenta, R1.Descripcion,
sldIniLocal.saldoAnt_L, Mvlocal.MvDebito_L, Mvlocal.MvCredito_L, SldFinlocal.SaldoActual_L
from cuentas as R1
left outer join sldIniLocal on SldIniLocal.CuentaLocal = R1.cuenta
left outer join mvLocal on mvLocal.cuentaLocal = R1.Cuenta
left outer join SldFinLocal on SldFinLocal.cuentaLocal = R1.Cuenta
where R1.catfcta in (1,2,3,6,7)
union
select R1.Cuenta, R1.Descripcion,
sldIniLocal.saldoAnt_L, Mvlocal.MvDebito_L, Mvlocal.MvCredito_L, SldFinlocal.SDACCTA_T_L [SaldoActual_L]
from cuentas as R1
left outer join sldIniLocal on SldIniLocal.CuentaLocal = R1.cuenta
left outer join mvLocal on mvLocal.cuentaLocal = R1.Cuenta
left outer join SldFinLocal on SldFinLocal.cuentaLocal = R1.Cuenta
where R1.catfcta in (4,5)),
AnexoFin as
(select R2.Cuenta, R2.Descripcion,
sum(R2.saldoAnt_L) [saldoAnt_L], sum(R2.MvDebito_L) [MvDebito_L], sum(R2.MvCredito_L) [MvCredito_L] , sum(R2.SaldoActual_L) [SaldoActual_L],
abs(sum(R2.saldoAnt_L)) + abs(sum(R2.MvDebito_L) ) + abs(sum(R2.MvCredito_L)) + abs(sum(R2.SaldoActual_L)) [indMvto]
from AnexoTemp as R2
group by cuenta, descripcion)
select R4.Cuenta, R4.Descripcion,
R4.saldoAnt_L, R4.MvDebito_L, R4.MvCredito_L, R4.SaldoActual_L
from AnexoFin as R4
where R4. indMvto <> 0
order by cuentaIt seems like I am answering the question by myself but, I really have some limitations: IT team confirmed that we can´t create Stored Procedures in this accounting software.
So, I am asking you if maybe you can show me another solution:
Do you know another way to avoid this warning when I run the TI ? OR
Do you know any way to say TM1 doesn't read this warning like an error?
Thank you very much for read and help.