Read SQL into Datastage job

[pavan_test]

Hi All,

I have a unique situation where a sql is stored in Oracle table.
The table has 4 columns, colA, colB, col C and colD.

The sql is stored in col D in the table.

sql statement in Col D might be something like this,

select xxx,yzz,ymz from table xx where condition 1= condition2

datastage has to read Col D (which contains sql) from the table into the ETL job and process the data.

Can someone please let me know how can I achieve this using Oracle Enterprise Edition stage, on aix server and datastage v8.1

Thanks
Pavan

[kandyshandy]

How many records in the oracle table whose col D has (a single SELECT) SQL? one or many?

[v2kmadhav]

are the number of columns in the sql query dynamic ? if its always 3 columns? What do you mean by process the data ? Would it be different for each table the data is extracted from ?

You could fetch the value in column D querying this table from a sequencer and pass the output sql as a parameter to the job that reads using a oracle stage perhaps using RCP...

[v2kmadhav]

are the number of columns in the sql query dynamic ? if its always 3 columns? What do you mean by process the data ? Would it be different for each table the data is extracted from ?

You could fetch the value in column D querying this table from a sequencer and pass the output sql as a parameter to the job that reads using a oracle stage perhaps using RCP...

[ray.wurlod]

Not easy, and I'd probably guess impossible in a single job. You can read the SQL from the table in one job, load it into that job's user status area, and read that when setting a job parameter to contain the SQL in a second job. Use the first job's $UserStatus activity variable to get the SQL statement.

[chulett]

... or write it to a file and have the second job read that 'SQL File'.

[pavan_test]

There will be only 1 record in the oracle table (lets say table name is myz) whose Col D has selct query.

The select query in Col D is not querying the same table. The select query will return 3 columns. The query is static for now, when there is a change, it might be in the "where" clause in the sql statement but not in select statement.

select xxx,yzz,ymz from table xx where condition 1= condition2

Thanks
Pavan

[kandyshandy]

There will be only 1 record in the oracle table (lets say table name is myz) whose Col D has selct query.

Then Craig/Ray has given you 2 options.

If it is more than one SQL with delimiter ';', then i don't think you can execute in enterprise stage, unless you implement a loop to pass one SQL at a time to enterprise stage.