Externalize SQL Query

[chulett]

Parameters and SQL Files are mutually exclusive. Only workaround I found was to maintain multiple sql file variations and use parameters in the filename to pull in the right one at the right time. Obviously limits just how dynamic they can be, however.

[vincentjeevan]

Thanks Craig. I uderstand the workaround. However, we have multiple filters and it will be difficult to maintain hunderds of QUERY file for a single Job.

[throbinson]

Build template SQL files with a token in place of the filter. sed the token to the desired parameter filters you want at runtime and redirect to a temp SQL file and run the Temp SQL in DataStage. Hopelessly complicated? You betcha! But it makes the SQL file(s) dynamic as opposed to static. Basically another level of abstraction and obfuscation.

[vincentjeevan]

Build template SQL files with a token in place of the filter. sed the token to the desired parameter filters you want at runtime and redirect to a temp SQL file and run the Temp SQL in DataStage. Hopelessly complicated? You betcha! But it makes the SQL file(s) dynamic as opposed to static. Basically another level of abstraction and obfuscation.

Thanks. I'll try this option.

[ray.wurlod]

Hi, I have a requirement to maintain all SQL Queries outside Datastage.

Resist stupid requirements.

[chulett]

Basically another level of abstraction and obfuscation.

Excellent! Nothing like a layer of abstration, followed by a nice chocolate creme and then some rich obfuscation to top it all off.

[vmcburney]

I have done this for a reference data job where I am pulling code and description from dozens of tables but it only works because all the source tables use the same metadata. Different SQL files and the table name and WHERE clause column name are passed in as job parameters.