TM1 Forum

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

I am surprised this,
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

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';

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" '

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

ET