Issue with Passing string parameter in single quotoes inTI process

[karthik.g]

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,

[David Usherwood]

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;

[karthik.g]

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

[tomok]

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.

[Wim Gielis]

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