Unable to use teradata or sql functions in table loader expression in sas DI

[Hercules]

Hello all,

I want to know whether can we use teradata or sql or Oracle functions in the expression field of table loader or teradata table loader in sas DI or not.

I was using teradata table loader and one of my column I was using teradata function (current_timestamp) inside expression of table loader.

It's throwing error as current_timestamp function is not found. Same case for if function also.

[Hercules]

No answer yet?

[Patrick · Accepted solution]

What can be done depends on your version of DI Studio. Besides of consulting the documentation what I'm often doing is having a look into the generated code. That normally tells you very fast what's working/not working and why.

From the documentation

SAS(R) Data Integration Studio 4.9: User's Guide

http://support.sas.com/resources/papers/proceedings10/116-2010.pdf

What you probably want to do is ELT instead of ETL - so you would load your source into a Teradata staging table first. Or you could load via a Teradata view which has the logic/transformation built in.

[Hercules]

Thank you Patrick for the kind reply.

Can you please specify why is it necessary to first load the data into a staging area (teradata table)  to use teradata functions in the expression of table loader?

Thanks

[Patrick]

When you load from a SAS source table into a target Teradata table and you're using functions in the expression field: Where would you expect that these function are getting used (on the SAS or Teradata side)?

I can only repeat: Look into the generated code and things will become obvious.

[Hercules]

Ok. So it means to use any of the sql or Oracle or teradata functions, it needs to be first staged into its respective table and then we can use its respective functions. That makes sense.

I had look into the code as well. Whenever I was using any of the expression inside table loader, it gets into the dataset I. E.

Data null;

Set W9fS0I;

Where current_timestamp ;

Run;

Something like that. And I was thinking, how can a teradata function will be used in sas dataset. And if that so, why sas has provided it.

Well thank you very much Patrick for the answer. I appreciate that.

[Patrick]

Some of the DI transformations allow you to define functions as part of explicit pass-through SQl. In such cases the SQL gets executed directly in the data base and so the functions you can use there are the data base functions.

It depends on the DIS version if you can explicitly define where stuff gets executed. It also depends on the contexts where you're using the DIS transformations (e.g. the SQL join). If it's about joining two data base tables then I believe it would generate directly data base specific code - so you could use data base functions.

With the SQL transformation: If it uses "implicit" SQL and formulating stuff in SAS syntax the SAS/Access engine for the database will still try and push processing to the data base. It then depends on the actual SAS functions used if this is possible or not. Use "OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;" in your code as this will tell you what syntax SAS actually could send to the data base for processing.

Also consult the documentation for the SAS version and SAS/Access engine used to find out which SAS functions can be translated into data base functions so processing can be pushed to the data base. SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Sixth Edition

[LinusH]

You could trigger ELT behaviour. From DI Studio Users Guide:

" When both the inputs and outputs of the Extract, SQL Join, Teradata Table Loader, and Table Loader transformations are stored in the same relational database, the code for these transformations can be pushed down to a database server for execution. This option increases performance by shifting data transformation to the most appropriate processing resource.

Note: The use of the Table Loader transformation in a pushdown job requires the following settings:

• Load style: select either Append to Existing or Replace

• New Rows: select Insert (SQL)"

Pushing ELT Job Code Down to a Database :: SAS(R) Data Integration Studio 4.9: User's Guide

http://support.sas.com/resources/papers/proceedings10/116-2010.pdf

[Hercules]

Thank you so much LinusH. I'll try this and share with you what happened.

But I've a string feeling that this will work.