SQL Query Error On TI Prolog tab

Post Reply
abhishek.abhi10sri
Posts: 8
Joined: Tue Jun 24, 2014 4:19 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: 2010

SQL Query Error On TI Prolog tab

Post by abhishek.abhi10sri »

Hi Experts

I getting error on prolog tab when I am trying to set ODBC SQL query as string.

Environment :
Cognos TM1 10.2.2
Datasource : ODBC

Here is the step what I am trying to do .

1- Open TI and selected Data source as ODBC.
2- Written a SQL show below
Select substr(clm1,1,2) ||'*' as clm1,
,clm2
From Tbl
3 - On Prolog tab I am trying to set ste2 query as string for that I have written following code

sSQLQry = 'Select substr(clm1,1,2) ||'*' as clm1,
,clm2
From Tbl
where clm1= 1'

datasourceQuery=sSQLqry;

4- I tried to run this process but I got the following error and this error coming because of clm1 which I trying to do substring then concat with '*'
so please let me know if any one faced similar issue.

4804 [f0] ERROR TM1.Process Process
Error: MetaData procedure line (0): Error executing SQL query: "sSQLQry = 'Select substr(clm1,1,2) ||'*' as clm1,
,clm2 From Tbl where clm1= 1"

Thanks
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: SQL Query Error On TI Prolog tab

Post by Mark RMBC »

I am surprised this,

Code: Select all

sSQLQry = 'Select substr(clm1,1,2) ||'*' as clm1,
,clm2 
From Tbl
where clm1= 1'
doesn’t throw up some sort of syntax error in the prolog as in version 10.1 at least I would think you need double quotes around the asterix.

This observation, assuming it is correct, makes me think we are not getting the full story here.

Cheers, Mark
abhishek.abhi10sri
Posts: 8
Joined: Tue Jun 24, 2014 4:19 pm
OLAP Product: Cognos TM1
Version: 10.2
Excel Version: 2010

Re: SQL Query Error On TI Prolog tab

Post by abhishek.abhi10sri »

Issue resolved by SQL function because in TM1 TM1 Api not able to recognize double pipe with Cache ODBC connection.

Correct SQL syntax

sSQLQry = 'Select reverse(stuff(reverse(clm_code),1,3,'''|'*'|''')) as CLM1
from Table name';
Drg
Regular Participant
Posts: 159
Joined: Fri Aug 12, 2016 10:02 am
OLAP Product: tm1
Version: 10.2.0 - 10.3.0
Excel Version: 2010

Re: SQL Query Error On TI Prolog tab

Post by Drg »

hey how DB you use???

maybe you need more quotes ' or "


sSQLQry = 'Select substr(clm1,1,2) ||''*'' as clm1,
,clm2
From Tbl
where clm1= ''1'' '
OR
sSQLQry = 'Select substr(clm1,1,2) ||''*'' as clm1,
,clm2
From Tbl
where clm1= "1" '
EvgenyT
Community Contributor
Posts: 324
Joined: Mon Jul 02, 2012 9:39 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: 2016
Location: Sydney, Australia

Re: SQL Query Error On TI Prolog tab

Post by EvgenyT »

I always use sQuote = CHAR (39)/CHAR(34) in cases when '/" required in SQL statement. Find easier to understand and debug.

ET
Post Reply