Query Help

Post Reply
Jorge Rachid
Posts: 113
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Query Help

Post by Jorge Rachid »

Hi guys,

I have a query that is running (attached) on sql server and after a copy and paste on tm1 turbo integrator it doesn't work! It's crazy and I don't know what to do anymore.

The message on log is "[SQL Server]Unclosed quotation mark after the character string ' '. " I've checked many times and there is not wrong.

------------------- Query -------------------------------

select ROW_NUMBER() OVER(ORDER BY Data_movto ASC) as 'Seq'
,ta.*
from (select convert(varchar,DATMOV,103) as Data_movto,
' ' as CARTEIRA,
PRD as produto,
case MOE
when '1' then 'POS'
when '0006' then 'POS'
when '10' then 'POS'
when 'PRE' then 'PRE'
else '?' end AS IDX,
MOE as moeda,
GRPPAP as grupo_papel,
DATEMS as data_emissao,
convert(varchar,DATVNC,103) as DATA_VENCTO,
PRZUTL as prazo_util,
99999 as COD_CRESP,
case
when sts='passivo' then VLRBRT
else abs(VLRBRT)
end as vlr_bruto,
case
when sts='passivo' then vlrpse
else abs(vlrpse)
end as VALOR_MOVTO,
STS as status,
CODOPE as cod_operacao,
DSCOPE as desc_operacao,
CLSPAP as classe_papel,
'Fluxo_caixa' as Tipo,
0 as menor_1,
0 as menor_16,
0 as menor_31,
0 as menor_61,
0 as menor_91,
0 as menor_180,
0 as maior_180
from dm01..utb_BSI_FLUPSVCXA
WHERE
DATMOV='2018-05-22'
AND PRD in ('CP-FUNCIONALISMO','OUTROS')

union all

SELECT convert(varchar,DATA_MOVTO,103) as Data_movto,
CASE CARTEIRA
WHEN 4 THEN 'EMPRESTIMO'
WHEN 9 THEN 'DESCONTO'
END AS CARTEIRA,
CASE COD_PRODUTO
WHEN 10 THEN 'CG-PRE'
WHEN 15 THEN 'HOT-MONEY'
WHEN 77 THEN 'HOT-MONEY CDI'
WHEN 13 THEN 'CG-CDI'
WHEN 400 THEN 'CP-FUNCIONALISMO'
WHEN 396 THEN 'CP-EMPRESA PRIVADA'
WHEN 175 THEN 'CP-PRE'
WHEN 130 THEN 'CDC-PJ VEICULOS'
ELSE 'OUTROS'
END AS PRODUTO,

CASE COD_PRODUTO
WHEN 10 THEN 'PRE'
WHEN 15 THEN 'PRE'
WHEN 77 THEN 'POS'
WHEN 13 THEN 'POS'
WHEN 400 THEN 'PRE'
WHEN 396 THEN 'PRE'
WHEN 175 THEN 'PRE'
WHEN 130 THEN 'POS'
ELSE 'PRE'
END AS IDX,
' ' as moeda,
0 as grupo_papel,
' ' as data_emissao,
convert(varchar, DATA_VENCTO,103),
0 as prazo_util,
COD_CRESP,
0 as vlr_bruto,
SUM(VALOR_MOVTO) AS VALOR_MOVTO,
' ' as status,
0 as cod_operacao,
' ' as desc_operacao,
' ' as classe_papel,
'Fluxo_empréstimo' as Tipo,
sum(menor1) as menor_1,
sum(menor16) as menor_16,
sum(menor31) as menor_31,
sum(menor61) as menor_61,
sum(menor91) as menor_91,
sum(menor180) as menor_180,
sum(maior180) as maior_180

FROM FLUXO_VAR F
WHERE DATA_MOVTO= '2018-05-22'
AND COD_PRODUTO not IN (10,15,77,13,396,175,130)
GROUP BY DATA_MOVTO, DATA_VENCTO, CARTEIRA, COD_PRODUTO, COD_CRESP) as ta

-------------------------------- End of query ----------------------------------------------------------------

Please help me!

JR
Attachments
query_running.jpg
query_running.jpg (301.25 KiB) Viewed 2904 times
tomok
MVP
Posts: 2831
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Query Help

Post by tomok »

It might be due to the question mark (?) in the query. TM1 could be thinking you are trying to insert a parameter.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/
Jorge Rachid
Posts: 113
Joined: Fri Jul 22, 2016 8:33 pm
OLAP Product: TM1
Version: 10.2
Excel Version: 2013

Re: Query Help

Post by Jorge Rachid »

It is that!
AMAZING tomok.
You saved me.
Thanks a lot man!!!
tomok wrote: Tue May 22, 2018 2:14 pm It might be due to the question mark (?) in the query. TM1 could be thinking you are trying to insert a parameter.
Post Reply