SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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

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.


Accepted Solutions
Solution
‎02-16-2013 02:17 AM
Respected Advisor
Posts: 4,173

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

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

View solution in original post


All Replies
Solution
‎02-16-2013 02:17 AM
Respected Advisor
Posts: 4,173

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

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

Occasional Contributor
Posts: 6

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

Thanks Patrick.

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

Occasional Contributor
Posts: 6

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

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?

Occasional Contributor
Posts: 6

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

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 624 views
  • 0 likes
  • 2 in conversation