TM1 Forum

Hi everyone,

I have a query on turbo integrator (ODBC connection it is ok and running and the query works fine on SQL Server Management Studio).

But some reason I got the error "SQL Statement Failed" when I try to put this on TM1. I have tried to found something on internet but nothing is clear enough. Can anyone help me?

Here goes the query that am facing problem:

----------------------------------------------------------------------
select
c.con_num_est as 'contrato'
,convert(varchar,dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111)),103) as 'data movimento'
,convert(varchar,p.prc_dat_vct,103) as 'data vencimento'
,convert(varchar,min(p.prc_dat_vct) over (partition by p.con_num),103) as 'minima data vencimento'
,(select count(*)
from [DWHTRA].[dbo].[DIMPOD_BI]
where datini between (dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111))) and p.prc_dat_vct
and CODSPRTIPPOD = 'D'
and INDDIAUTL = 1) as 'dias úteis'
,case when (min(p.prc_dat_vct) over (partition by p.con_num)
<
dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111))))
then datediff(day,min(p.prc_dat_vct) over (partition by p.con_num),(dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111))))
else 0
end as 'calculo'
,0 as 'código correspondente'
,isnull(ind.cod_ind,0) as 'código moeda'
,c.prodbc_cod as 'código produto'
,prodbc_des as 'descrição do produto'
,convert(float,case con_tip_tax when 1 then p.prc_val_sdoger else p.prc_val_vct end) as 'valor movimento'
,4 as 'carteira'
,'att' as 'sistema'
,prod.prodbc_abv as 'modalidade'
,cdgdesinfo as 'status do contrato'
,c.con_tip_tax
,isnull(c.con_cod_indctb,0) as con_cod_indctb
,p.prc_num
,convert(float,p.prc_val_renapr60) as prc_val_renapr60
,c.emp_cod
,c.depend_cod
,c.con_idc_vctutil
,c.con_val_taxctb
,c.con_und_taxctb
,c.con_pct_indctb
,p.prc_idc_compgt
,c.con_num
,convert(varchar,c.con_dat,103) as con_dat
,convert(varchar,c.con_dat_vct,103) as con_dat_vct
from tb_con as c (nolock)
join tb_prodbc as prod (nolock) on prod.prodbc_cod = c.prodbc_cod
join tb_prcbkp as p (nolock) on p.con_num = c.con_num
and p.prc_dat_alt = c.con_dat_alt
join (select min(prc_dat_vct) as date
,con_num
from tb_prcbkp
group by con_num
) as p2 on p.con_num = p2.con_num
join tb_mod as m (nolock) on c.prodbc_cod = m.prodbc_cod
join tb_lan as l (nolock) on l.con_num = p.con_num
and l.prc_num = p.prc_num
and l.lan_dat_alt = p.prc_dat_alt
left join tb_indice as i (nolock) on c.con_cod_indctb = i.indice_cod
left join tb_ind as ind (nolock) on i.indice_ref = ind.ref_ind
left join emptblcdgcoddesglobal as g (nolock) on cdgcodgrupo = 2
and c.sitcon_cod = g.cdgcodinfo
where (c.con_dat_alt = '1-1-1960 0:0:0.000')
and ((c.sitcon_cod in (1, 2, 3, 4))
or (c.sitcon_cod = 7
and c.sitcon_dat >= dateadd(day, 1, dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111)))))
and (c.con_dat <= dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111)))
and (m.mod_idc_comissao is null or m.mod_idc_comissao <> 's')
and (m.mod_idc_conrep is null or m.mod_idc_conrep <> 's')
and (m.mod_idc_confin is null or m.mod_idc_confin <> 's')
and (m.mod_idc_fiahon is null or m.mod_idc_fiahon <> 's')
and (m.mod_idc_fiapas is null or m.mod_idc_fiapas <> 's')
and (m.mod_idc_vartom is null or m.mod_idc_vartom <> 's')
and (m.mod_idc_compror is null or m.mod_idc_compror <> 's')
and (m.mod_idc_cdci is null or m.mod_idc_cdci <> 's')
and (m.mod_idc_jurspl is null or m.mod_idc_jurspl <> 's')
and (p.prcbkp_dat = dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111)))
and (l.lan_dat_pgt is null or l.lan_dat_pgt >= dateadd(day, 1, dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111))))
and l.lan_seq_par = (select max(l1.lan_seq_par)
from tb_lan l1
where (l.con_num = l1.con_num)
and (l.prc_num = l1.prc_num)
and (l.lan_dat_alt = l1.lan_dat_alt)
and (l.lan_tip = l1.lan_tip)
)
and (l.lan_tip = 3)
and cdgdesinfo <> 'Em Prejuízo'
order by
c.con_num_est,
p.prc_dat_vct asc,
c.con_cod_indctb asc,
c.prodbc_cod asc

-------------------------------------------------------

Best regards,

JR

Does the user you are using to connect to the DB via ODBC have access to the table? Keep in mind that when you run it in Studio you will be running it as you,

Jim.

Yes,

I ran it on studio with the same user that I am using on Turbo Integrator.

I think that query is using something that TM1 might not accept, don't know.

Thank you for the reply.

Best regards.

Hi,

does a simple select * from tb_prcbkp work?

How is the ODBC connection made? Which user are you using in the TI?

Maybe it is better do save your (not thast easy sql statement) as a view in the SQL DB.
Then you need only a select * from myview in the TI

Regards,
Roland

You get a much better error-message in file tm1server.log. Have a look there.

My very first assumption would be the single "<" in one line. The syntax-interpreter of ODBC and TM1 is different to the studio one.

Hi Willi, I can't get any log because the process is not running. I put the query on the process and press preview to see if it works, but i've checked now and there is no log file of doing that.

Do you know where can I find a tutorial that shows me what I can't use on query on TM1?

Thanks!

Willi wrote: ↑Wed May 09, 2018 11:12 am You get a much better error-message in file tm1server.log. Have a look there.

My very first assumption would be the single "<" in one line. The syntax-interpreter of ODBC and TM1 is different to the studio one.

Hi Orlando. Yes, when I try select * from any table works fine. The user on TM1 is the same that I tested on sql server management studio. The ODBC connection was made in windows, just selecting the database, user and password.

Sorry about my ignorance, but how it works about creating a view in SQL ?

Thanks for helping.

Best regards.

orlando wrote: ↑Wed May 09, 2018 8:23 am Hi,

does a simple select * from tb_prcbkp work?

How is the ODBC connection made? Which user are you using in the TI?

Maybe it is better do save your (not thast easy sql statement) as a view in the SQL DB.
Then you need only a select * from myview in the TI

Regards,
Roland

Jorge Rachid wrote: ↑Wed May 09, 2018 11:26 am Hi Willi, I can't get any log because the process is not running. I put the query on the process and press preview to see if it works, but i've checked now and there is no log file of doing that.

Do you know where can I find a tutorial that shows me what I can't use on query on TM1?

Thanks!

Did you checked the tm1server.log for the tm1-server? If there is nothing adjust the logging-level.

Hi guys,

I think I realize what is the problem.

The query get data from the database FINFINDBS_PRD, but in the middle of the query we get data from another database as you can see in code:

-------------------------------

,(select count(*)
from [DWHTRA].[dbo].[DIMPOD_BI]
where datini between (dateadd(day,-1,convert(datetime,convert(varchar,getdate(),111),111))) and p.prc_dat_vct
and CODSPRTIPPOD = 'D'
and INDDIAUTL = 1)

-------------------------------

When I took off this part of the query it worked, so I can imagine that is not possible to use data from another database on tm1 query, right?

Thanks!

JR

Wrong.

If you do it syntactically correct and the user has the permission it is correct.

Again: look at the logfile.

It's not the user YOU are logged on as when defining the query in TM1, it is the account the TM1 SERVICE is running under (assuming you are using SQL Server integrated security). If you take that table out and it works it's probably that the TM1 service account does not have access to that table in SQL Server.

Willi wrote: ↑Wed May 09, 2018 12:55 pm Wrong.

If you do it syntactically correct and the user has the permission it is correct.

Again: look at the logfile.

Hi, I take a look at the log and I got this:

2620 [c] ERROR 2018-05-09 14:24:01.679 TM1.SQLAPI 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.
2620 [c] ERROR 2018-05-09 14:24:03.914 TM1.SQLAPI 37000[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'as'.

Uhul! It worked! I checked again and there were one more "as 'data'" at the end of one specific line. It was a repetead code.

Thanks a lot guys!

Jorge Rachid wrote: ↑Wed May 09, 2018 11:29 am Hi Orlando. Yes, when I try select * from any table works fine. The user on TM1 is the same that I tested on sql server management studio. The ODBC connection was made in windows, just selecting the database, user and password.

Sorry about my ignorance, but how it works about creating a view in SQL ?

Thanks for helping.

Best regards.

Hi Jorge,

in MSSQL try this: https://docs.microsoft.com/en-us/sql/t- ... erver-2017
other SQL DB are similar

Best regards,
orlando