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

[steve_db]

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

                                       _

                                       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.

[Patrick · Accepted solution]

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

[steve_db]

Thanks Patrick.

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

[steve_db]

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?

[steve_db]

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.