Teradata Connector

wjblack7478 · Post by **wjblack7478** » Thu Apr 03, 2014 12:32 pm

Is it possible to read SQL from a file in the Teradata connector? If so where is the file name to be specified in the connector.

chulett · Post by **chulett** » Thu Apr 03, 2014 1:52 pm

Perhaps... there isn't always a specific option to do so but see if this helps.

wjblack7478 · Post by **wjblack7478** » Thu Apr 03, 2014 2:33 pm

There doesn't appear to be an option for the Teradata connector. Anyone know of one or another way to accomplish this?

chulett · Post by **chulett** » Thu Apr 03, 2014 2:44 pm

The whole point of the linked conversation is to note that there doesn't need to be a specific option for this to work. Can't tell from what you posted - did you try telling it to use User-Defined / Custom SQL and then put {FILE} and the file's pathname where the SQL would normally go?

wjblack7478 · Post by **wjblack7478** » Thu Apr 03, 2014 3:23 pm

Yes, I understand what a linked conversation is and how to use it. The point is I wasn't able to find anything remotely close within the Teradata connector that allowed one to enter user defined SQL. I may have missed it. Can you tell me where to plug in user defined SQL?

wjblack7478 · Post by **wjblack7478** » Thu Apr 03, 2014 3:26 pm

Are you saying that '{FILE}/pathnameofsqlfile' will work if I add it to the select statement?

chulett · Post by **chulett** » Thu Apr 03, 2014 3:41 pm

All I'm saying is that it is known to work in other stages and thought it was worth a shot in the Teradata Connector. I personally don't have any way to test or confirm that but if you can find a way to put your own custom SQL into the stage, then this syntax may allow it to be read from a file instead.

ray.wurlod · Post by **ray.wurlod** » Thu Apr 03, 2014 5:55 pm

Before-SQL and After-SQL can certainly be read from a file using Teradata Connector (use Before SQL File or After SQL File properties).

There seems to be no way to use a file to contain a SELECT statement. Job parameter(s) may, of course, be used, so you may have a workaround there.

If the Teradata Connector has an input link, change the write mode to User-Defined SQL, change the User-Defined SQL property to File and this enables the File property, in which you may insert the pathname of a text file that contains the requisite SQL.

The usual caveats about matching the metadata continue to apply where this technique is used.

chulett · Post by **chulett** » Thu Apr 03, 2014 6:42 pm

Was just coming here to post the contents of the File option for the connector.

File

Use this property to specify the name of a text file that contains the SQL statements to run for each input row. Include the path if the file is not in the project directory. This property is required when the User-defined SQL property is set to File. The file must be on the conductor node and must be in the character set in the Character set property or the Client character set property if the Character set property is empty. Any line terminators must be appropriate for the conductor platform.

You can use this property to run dynamic SQL for each input row. If you want to send row counts to the reject link, you can use this property to run dynamic SQL instead of using the Before SQL file or After SQL file properties.

I was wondering if a target only option is what was implied by the "contains the SQL statements to be run for each input row."

ray.wurlod · Post by **ray.wurlod** » Thu Apr 03, 2014 7:31 pm

chulett wrote:I was wondering if a target only option is what was implied by the "contains the SQL statements to be run for each input row."

I believe so, confirmed by my playing with the stage concerned (version 8.7).

chulett · Post by **chulett** » Thu Apr 03, 2014 9:07 pm

Still wondering... isn't there an option in the Connector to supply your own SQL when using it as a source?

ray.wurlod · Post by **ray.wurlod** » Fri Apr 04, 2014 12:09 am

There doesn't appear to be, either in the stage or in the documentation.