TM1 Forum

Posted: **Wed Feb 19, 2020 1:48 pm**

Hi,

I have problem with TI process retrieving data from MS SQL Server database.

Through couple month I had properly working process. There where Datasource query:

Code: Select all

SELECT 
 [GJAHR]
,[MONAT]
,[LDGRP] 
,[HKONT] 
,[ZZPRODUCT] 
,[ZZDISTCHAN] 
,[ZZACCIDENT]
,[ZZSOURCE] 
,[ZZACTIVITY]
,[ZZREINSCON]
,[ZZIP]
,sum([DMBTR]) as Amount
  FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS]
where [LDGRP] not in ('S2') 
    group by 
       [GJAHR]
      ,[MONAT]
      ,[LDGRP]
      ,[HKONT]
      ,[ZZPRODUCT]
      ,[ZZDISTCHAN]
      ,[ZZACCIDENT]
      ,[ZZSOURCE]
      ,[ZZACTIVITY]
      ,[ZZREINSCON]
      ,[ZZIP];

and Advanced\Prolog query:

Code: Select all

DataSourceQuery = 

'SELECT 
[GJAHR]
,MONAT
,[LDGRP] 
,[HKONT] 
,[ZZPRODUCT] 
,[ZZDISTCHAN] 
,[ZZACCIDENT]
,[ZZSOURCE] 
,[ZZACTIVITY]
,[ZZREINSCON]
,[ZZIP]
,sum([DMBTR]) as Amount
  FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS]
where [LDGRP] not in (''S2'') 
and GJAHR = ' |  P_Rok | '
and MONAT = ' | P_Miesiac | '
    group by 
       [GJAHR]
      ,MONAT
      ,[LDGRP]
      ,[HKONT]
      ,[ZZPRODUCT]
      ,[ZZDISTCHAN]
      ,[ZZACCIDENT]
      ,[ZZSOURCE]
      ,[ZZACTIVITY]
      ,[ZZREINSCON]
      ,[ZZIP]';

I also manipulated data in variables (and Advanced\Data tab):

Code: Select all

#****Begin: Generated Statements***
vScenario=subst (GJAHR, 1, 4) | ' Actual';
vMonth=MONAT;
vInterval='MTD';
vLedger=if (LDGRP @= '', 'NULL', LDGRP);
vProduct=if(ZZPRODUCT @= '', 'NN', ZZPRODUCT);
vDC=if (ZZDISTCHAN @= '', 'NN', ZZDISTCHAN);
vSource=if (ZZSOURCE @= '', 'NN', ZZSOURCE);
vActivity=if (ZZACTIVITY @= '', 'NULL', ZZACTIVITY);
vReCon= if (ZZREINSCON @='2019_QS_MTPL', '2019_QS_MTPL', 'NN');
vIP=if(ZZIP @= '', 'NULL', ZZIP);
vMeasure='Reported';
vAccount=HKONT;
vAccidentYear=if (ZZACCIDENT @= ''  % ZZACCIDENT @= '0000', 'NN', if( StringToNumber(ZZACCIDENT)>= StringToNumber(GJAHR), 'CY', if (StringToNumber (ZZACCIDENT) < StringToNumber (GJAHR), 'PY', 'NN')));
#****End: Generated Statements****

All above mentioned was working properly, but the source changed.
Now I have to add additional fields and condition on them.
I wanted to do it in similar way:

Datasource query:

Code: Select all

SELECT 
 ZapisySap.[GJAHR]
,ZapisySap.[MONAT]
,ZapisySap.[LDGRP] 
,ZapisySap.[HKONT] 
,ZapisySap.[ZZPRODUCT] 
,ZapisySap.[ZZDISTCHAN] 
,ZapisySap.[ZZACCIDENT]
,ZapisySap.[ZZSOURCE] 
,ZapisySap.[ZZACTIVITY]
,ZapisySap.[ZZREINSCON]
,ZapisySap.[ZZIP]
,ZapisySap.AEDAT
,ZapisySap.UTIME
,ZapisySap.BELNR
,sum(ZapisySap.[DMBTR]) as Amount
  FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySap
where ZapisySap.[LDGRP] not in ('S2') 
AND ZapisySap.HKONT = '0640200001'
    group by 
       ZapisySap.[GJAHR]
      ,ZapisySap.[MONAT]
      ,ZapisySap.[LDGRP]
      ,ZapisySap.[HKONT]
      ,ZapisySap.[ZZPRODUCT]
      ,ZapisySap.[ZZDISTCHAN]
      ,ZapisySap.[ZZACCIDENT]
      ,ZapisySap.[ZZSOURCE]
      ,ZapisySap.[ZZACTIVITY]
      ,ZapisySap.[ZZREINSCON]
      ,ZapisySap.[ZZIP]
      ,ZapisySap.[AEDAT]
      ,ZapisySap.[UTIME]
      ,ZapisySap.[BELNR];

and Advanced\Prolog query:

DataSourceQuery =

Code: Select all

'SELECT 
 ZapisySap.[GJAHR]
,ZapisySap.[MONAT]
,ZapisySap.[LDGRP] 
,ZapisySap.[HKONT] 
,ZapisySap.[ZZPRODUCT] 
,ZapisySap.[ZZDISTCHAN] 
,ZapisySap.[ZZACCIDENT]
,ZapisySap.[ZZSOURCE] 
,ZapisySap.[ZZACTIVITY]
,ZapisySap.[ZZREINSCON]
,ZapisySap.[ZZIP]
,sum([DMBTR]) as Amount
     FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySap

     [b]LEFT JOIN (SELECT BELNR, GJAHR, MAX(AEDAT+UTIME) AS Wersja FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] 

       WHERE GJAHR = ' |  P_Rok | '
       AND MONAT = ' | P_Miesiac | '
       GROUP BY BELNR, GJAHR) AS OSTATNIA_WERSJA_DOK ON ((OSTATNIA_WERSJA_DOK.BELNR = ZapisySap.BELNR) AND ((ZapisySap.AEDAT + 
       ZapisySap.UTIME) = OSTATNIA_WERSJA_DOK.Wersja))[/b]

WHERE ZapisySap.GJAHR = ' |  P_Rok | '
AND ZapisySap.MONAT = ' | P_Miesiac | '
[b]AND OSTATNIA_WERSJA_DOK.Wersja = (ZapisySap.AEDAT + ZapisySap.UTIME)[/b]
AND ZapisySap.LDGRP not in (''S2'') 
GROUP BY 
 ZapisySap.[GJAHR]
,ZapisySap.[MONAT]
,ZapisySap.[LDGRP] 
,ZapisySap.[HKONT] 
,ZapisySap.[ZZPRODUCT] 
,ZapisySap.[ZZDISTCHAN] 
,ZapisySap.[ZZACCIDENT]
,ZapisySap.[ZZSOURCE] 
,ZapisySap.[ZZACTIVITY]
,ZapisySap.[ZZREINSCON]
,ZapisySap.[ZZIP]';

Process is finishing with info "Process aborted"

In Message log there are info:

7204 [4] ERROR 2020-02-19 13:43:04.072 TM1.Process Process "A.LOAD_GL_SAP_NEW": : Execution was aborted. Error file: <TM1ProcessError_20200219134304_07207204_A.LOAD_GL_SAP_NEW.log>
7204 [4] ERROR 2020-02-19 13:43:04.072 TM1.SQLAPI 01000[Microsoft][SQL Server Native Client 11.0][SQL Server]Executing SQL directly; no cursor.

and:
Error: MetaData procedure line (0): Error executing SQL query: "SELECT ZapisySap.[GJAHR],ZapisySap.[MONAT],ZapisySap.[LDGRP] ,ZapisySap.[HKONT] ,ZapisySap.[ZZPRODUCT] ,ZapisySap.[ZZDISTCHAN] ,ZapisySap.[ZZACCIDENT],ZapisySap.[ZZSOURCE] ,ZapisySap.[ZZACTIVITY],ZapisySap.[ZZREINSCON],ZapisySap.[ZZIP],sum([DMBTR]) as AmountFROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySapLEFT JOIN (SELECT BELNR, GJAHR, MAX(AEDAT+UTIME) AS Wersja FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] WHERE GJAHR = 2020 AND MONAT = 02 GROUP BY BELNR, GJAHR) AS OSTATNIA_WERSJA_DOK ON ((OSTATNIA_WERSJA_DOK.BELNR = ZapisySap.BELNR) AND ((ZapisySap.AEDAT + ZapisySap.UTIME) = OSTATNIA_WERSJA_DOK.Wersja))WHERE ZapisySap.GJAHR = 2020AND ZapisySap.MONAT = 02AND OSTATNIA_WERSJA_DOK.Wersja = (ZapisySap.AEDAT + ZapisySap.UTIME)AND ZapisySap.LDGRP not in ('S2') GROUP BY ZapisySap.[GJAHR],ZapisySap.[MONAT],ZapisySap.[LDGRP] ,ZapisySap.[HKONT] ,ZapisySap.[ZZPRODUCT] ,ZapisySap.[ZZDISTCHAN] ,ZapisySap.[ZZACCIDENT],ZapisySap.[ZZSOURCE] ,ZapisySap.[ZZACTIVITY],ZapisySap.[ZZREINSCON],ZapisySap.[ZZIP]"

If Im'm not clear, let me know, pls. I also can share screens and additional informations.

In SQL Server Management Studio both queries (from Prolog) are working properly.

Posted: **Wed Feb 19, 2020 2:23 pm**

Looking at the error I would check your spacing, for example you have

Code: Select all

sum([DMBTR]) as AmountFROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySapLEFT

There needs to be a space between Amount and From and ZapisySap and Left

There are other spacing issues so I would sort that out first

regards, Mark

Posted: **Wed Feb 19, 2020 2:34 pm**

Mark RMBC wrote: ↑Wed Feb 19, 2020 2:23 pm Looking at the error I would check your spacing, for example you have
Code: Select all
sum([DMBTR]) as AmountFROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySapLEFT 
There needs to be a space between Amount and From and ZapisySap and Left

There are other spacing issues so I would sort that out first

regards, Mark

The last part of my describe is copy/paste from Message Log.
Please look rather on the parts, which I marked as Code.

Posted: **Wed Feb 19, 2020 3:40 pm**

djarko wrote: ↑Wed Feb 19, 2020 2:34 pm
Mark RMBC wrote: ↑Wed Feb 19, 2020 2:23 pm Looking at the error I would check your spacing, for example you have
Code: Select all
sum([DMBTR]) as AmountFROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySapLEFT 
There needs to be a space between Amount and From and ZapisySap and Left

There are other spacing issues so I would sort that out first

regards, Mark
The last part of my describe is copy/paste from Message Log.
Please look rather on the parts, which I marked as Code.

I would go with Mark on this - just because your code reads nicely with lines breaks etc. this does not necessarily mean this is what is executed.
I always set a variable to the SQL statement so that I can asciioutput it if needed and see for certain what is being executed/sent. Be very careful of TM1 and how it treats "white space", spaces etc.

Posted: **Wed Feb 19, 2020 3:56 pm**

djarko wrote: ↑Wed Feb 19, 2020 2:34 pm The last part of my describe is copy/paste from Message Log.
Please look rather on the parts, which I marked as Code.

If this is copied verbatim from the message log:

Error: MetaData procedure line (0): Error executing SQL query: "SELECT ZapisySap.[GJAHR],ZapisySap.[MONAT],ZapisySap.[LDGRP] ,ZapisySap.[HKONT] ,ZapisySap.[ZZPRODUCT] ,ZapisySap.[ZZDISTCHAN] ,ZapisySap.[ZZACCIDENT],ZapisySap.[ZZSOURCE] ,ZapisySap.[ZZACTIVITY],ZapisySap.[ZZREINSCON],ZapisySap.[ZZIP],sum([DMBTR]) as AmountFROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] ZapisySapLEFT JOIN (SELECT BELNR, GJAHR, MAX(AEDAT+UTIME) AS Wersja FROM [SAP_Eksport].[dbo].[VE_Z_XTRACT_DOCUMENTS] WHERE GJAHR = 2020 AND MONAT = 02 GROUP BY BELNR, GJAHR) AS OSTATNIA_WERSJA_DOK ON ((OSTATNIA_WERSJA_DOK.BELNR = ZapisySap.BELNR) AND ((ZapisySap.AEDAT + ZapisySap.UTIME) = OSTATNIA_WERSJA_DOK.Wersja))WHERE ZapisySap.GJAHR = 2020AND ZapisySap.MONAT = 02AND OSTATNIA_WERSJA_DOK.Wersja = (ZapisySap.AEDAT + ZapisySap.UTIME)AND ZapisySap.LDGRP not in ('S2') GROUP BY ZapisySap.[GJAHR],ZapisySap.[MONAT],ZapisySap.[LDGRP] ,ZapisySap.[HKONT] ,ZapisySap.[ZZPRODUCT] ,ZapisySap.[ZZDISTCHAN] ,ZapisySap.[ZZACCIDENT],ZapisySap.[ZZSOURCE] ,ZapisySap.[ZZACTIVITY],ZapisySap.[ZZREINSCON],ZapisySap.[ZZIP]"

then you indeed have multiple instances where you need a space to separate your SQL commands.

Code: Select all

AmountFROM

is not the same as

Code: Select all

Amount FROM

A line break in your TM1 code does not equal a space in the string that is generated from said code. If you don't believe me, take away all the line breaks in your code where you are constructing the DataSourceQuery variable and you will see.

Posted: **Wed Feb 19, 2020 4:16 pm**

Mark RMBC, gtonkin,

You're completely right. There where white scpaces etc.

Thanks.

TM1 Forum

SQL Query - ODBC or syntax issue?

SQL Query - ODBC or syntax issue?

Re: SQL Query - ODBC or syntax issue?

Re: SQL Query - ODBC or syntax issue?

Re: SQL Query - ODBC or syntax issue?

Re: SQL Query - ODBC or syntax issue?

Re: SQL Query - ODBC or syntax issue?