TM1 Forum

Posted: **Fri Apr 15, 2016 8:51 am**

Hello Experts,

I've created a TI process to get the data from Teradata. In my Data source tab, i defined the query to pull only Top 10 rows from the table and I'm overwriting my query in prolog tab using a where clause.

When I run the process i would like to automatically pass the season based on Current month.

Code: Select all


Code in Prolog is like:

vCurrentSeason = TRIM(Timst (Now , ' \Y\m' ) ) ;

IF (SUBST (vCurrentSeason, 5,2) @= '01' );

	vNewCurrentSeason =  'SP' | SUBST (vCurrentSeason, 1,4) ;

	ELSEIF (SUBST (vCurrentSeason, 5,2) @= '02' );
	vNewCurrentSeason =  'SP' | SUBST (vCurrentSeason, 1,4) ;

	and so on...
ENDIF;
 
 vPrevseason= ATTRS ('Season' , vNewCurrentSeason , 'PrevSeason');


DatasourceQuery = Expand ( ' 
SELECT Geo, Div,  Category_Cd,  Bus_Sesn_Yr_Cd_SSYYYY,  Units,  Value FROM DDC.Actuals where Bus_Sesn_Yr_Cd_SSYYYY  = %vPrevSeason% ; ');

When i run the process i'm getting an error

Code: Select all

 Error: Data procedure line (0): Error executing SQL query: " SELECT Geo, Div,  Category_Cd,  Bus_Sesn_Yr_Cd_SSYYYY,  Units,  Value FROM DDC.Actuals where Bus_Sesn_Yr_Cd_SSYYYY  = SP2016; "

i think SP2016 should be passed in Single quotes to run the query successfully.

Could anyone let me know, how to deal with this situation?

Thanks,

Posted: **Fri Apr 15, 2016 9:05 am**

i think SP2016 should be passed in Single quotes to run the query successfully.
Could anyone let me know, how to deal with this situation?

You've answered your own question

You can either 'escape' the single quote symbol:

vNewCurrentSeason = '''SP' | SUBST (vCurrentSeason, 1,4) | '''';

or (my preference) define a variable to hold the single quote:

# in prolog
vQu = char(39);

vNewCurrentSeason = vQu |'SP' | SUBST (vCurrentSeason, 1,4) | ' vQu;

Posted: **Fri Apr 15, 2016 11:22 am**

You can either 'escape' the single quote symbol:
vNewCurrentSeason = '''SP' | SUBST (vCurrentSeason, 1,4) | '''';

Thanks David. i tried this but unfortunately this didn't worked

because i'm taking the previous season based on vNewCurrentseason and passing it to query.

vPreviousSeason = ATTRS ('Season' , vNewCurrentSeason , 'Previous Season in Year');

So instead of using escape character in vNewcurrentseason i used in the query itself. It solved the problem.

Code: Select all

DatasourceQuery = Expand ( ' 
SELECT Geo, Div,  Category_Cd,  Bus_Sesn_Yr_Cd_SSYYYY,  Units,  Value FROM DDC.Actuals where Bus_Sesn_Yr_Cd_SSYYYY  = ''%vPrevSeason%'' ; ');

Posted: **Fri Apr 15, 2016 12:55 pm**

karthik.g wrote:So instead of using escape character in vNewcurrentseason i used in the query itself. It solved the problem.

Don't miss the forest for the trees. It's not about your EXACT situation. The point to take away from this is that the apostrophe is a reserved character in TM1 so whenever you need to use it in a string you need to have a workaround. David's example of using the ASCII character code as a replacement is one example of such a workaround.

Posted: **Sun Apr 17, 2016 9:12 pm**

See also, a very short article:
http://users.skynet.be/fa436118/wim/tm1 ... ote_EN.htm

TM1 Forum

Issue with Passing string parameter in single quotoes inTI process

Issue with Passing string parameter in single quotoes inTI process

Re: Issue with Passing string parameter in single quotoes inTI process

Re: Issue with Passing string parameter in single quotoes inTI process

Re: Issue with Passing string parameter in single quotoes inTI process

Re: Issue with Passing string parameter in single quotoes inTI process