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
SQL Query Error On TI Prolog tab
-
- Posts: 8
- Joined: Tue Jun 24, 2014 4:19 pm
- OLAP Product: Cognos TM1
- Version: 10.2
- Excel Version: 2010
-
- 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
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
Code: Select all
sSQLQry = 'Select substr(clm1,1,2) ||'*' as clm1,
,clm2
From Tbl
where clm1= 1'
This observation, assuming it is correct, makes me think we are not getting the full story here.
Cheers, Mark
-
- 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
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';
Correct SQL syntax
sSQLQry = 'Select reverse(stuff(reverse(clm_code),1,3,'''|'*'|''')) as CLM1
from Table name';
-
- 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
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" '
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" '
-
- 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
I always use sQuote = CHAR (39)/CHAR(34) in cases when '/" required in SQL statement. Find easier to understand and debug.
ET
ET