SQL ANSI_NULLS Warnings are missunderstood like an error in turbointegrator

Post Reply
jjramirez
Posts: 31
Joined: Fri Jan 13, 2017 12:33 pm
OLAP Product: IBM Planning Analytics/TM1
Version: 2.0.9.15
Excel Version: Excel 2019

SQL ANSI_NULLS Warnings are missunderstood like an error in turbointegrator

Post by jjramirez »

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:
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.
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.

The first solution is to set ansi_warnings OFF in the execution time.This is possible with just execute this instruction:
SET ANSI_WARNINGS OFF
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...

This is the query working:

Image

This is the error in log when the case happen:

Image

This is the error when I try to fix it adding the instruction:

Image

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 cuenta

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.
Attachments
adding SET instruction before.png
adding SET instruction before.png (53.19 KiB) Viewed 1724 times
error_caused_by_warning.png
error_caused_by_warning.png (9.05 KiB) Viewed 1724 times
Actual SQL Query OK.png
Actual SQL Query OK.png (74.85 KiB) Viewed 1724 times
Post Reply