topic how to use a Teradata user written function in a Join's target column expression in SAS Data Management

how to use a Teradata user written function in a Join's target column expression

steve_db — Fri, 15 Feb 2013 18:09:23 GMT

I'm new to SAS DI, using 4.2.

i have a need to call a Teradata function for a Target column expression in a Join transformation.

for example:

I mapped the source columns

RNT_AGRS.due_ci_tmsp and OPTMZ_GRP_BR_CURR_AIRPT.grp_br_tmz_cde

to the target column;

the expression:

the Target column expression is:

SYSLIB.ERAC_LCL_TO_GMT(RNT_AGRS.due_ci_tmsp, OPTMZ_GRP_BR_CURR_AIRPT.grp_br_tmz_cde

) as EVNT_GMT_TSP length = 8

at run time SAS DI complains:

SYSLIB.ERAC_LCL_TO_GMT(RNT_AGRS.due_ci_tmsp, OPTMZ_GRP_BR_CURR_AIRPT.grp_br_tmz_cde

) as EVNT_GMT_TSP length = 8

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>

, =, >, >=, ?, AND, BETWEEN,

CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~

ERROR 76-322: Syntax error, statement will be ignored.

Re: how to use a Teradata user written function in a Join's target column expression

Patrick — Sat, 16 Feb 2013 07:17:45 GMT

You must make sure that the SQL executes on the Teradata side. You can achieve this by selecting "yes" for the pass through join options as shown in Figure 12 in the linked paper http://support.sas.com/resources/papers/proceedings10/116-2010.pdf

Re: how to use a Teradata user written function in a Join's target column expression

steve_db — Mon, 18 Feb 2013 12:52:57 GMT

Thanks Patrick.

the picture in Figure 12 doesn't resemble what i see when I look at the join:

Re: how to use a Teradata user written function in a Join's target column expression

steve_db — Mon, 18 Feb 2013 14:11:16 GMT

when I right click the Join and click on Properties, I check the option "Use the Optimized pass-through facility for SQL statements";

when I run with this, the column using the function errors with:

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, , =, , =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

2994	SYSLIB.ERAC_LCL_TO_GMT(RNT_AGRS.due_ci_tmsp , OPTMZ_GRP_BR_CURR_AIRPT.grp_br_tmz_c

de ) as EVNT_GMT_TSP length = 8

	_
	22
	76

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>

, =, >, >=, ?, AND, BETWEEN,

CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~

ERROR 76-322: Syntax error, statement will be ignored.

2995	format = Datetime.
2996	informat = Datetime.
2997	label = 'gmt timestamp when rental was due to be returned',

this SQL statement is successfully tested in Teradata directly, but will not run for DIS?

Re: how to use a Teradata user written function in a Join's target column expression

steve_db — Mon, 18 Feb 2013 19:51:15 GMT

thank you Patrick -

turns out that your answer was correct. I couldn't see the properties pane, and didn't know that I had to click on the menu item in the Join to "Show Properties Pane";

once I did that and selected pass-through for the Join - all went well...

thanks.