records as parameter to DB

[kennyapril]

Hello everyone,

I have a column by name table_name which has all the table names in it,I need to send those table names as parameter to a select query in an ODBC stage.

Please let me know how can I pass that column records to the table name in that query

Thank you!

[asorrell]

Are they on the same database? If so, I'd leave DataStage out of it, just use a SQL sub-query to grab the first list of names and feed it to the main query. If it requires more complexity then I'd use a stored procedure. You can then execute a Stored Procedure Stage to get the data from that sequence into a job if you need it.

If not, then you'll need two separate jobs in a job sequence. First job grabs the names and passes them back to the job sequence, which feeds them to the next job as a a parameter.

If you search you should find several posts on feeding data back to a job sequence for the next job. I'd suggest writing a simple Server job that sets the UserStatus variable to the list of names. Then it can be fed to the parallel job for processing.

[suryadev]

Thank you Andy!

Yes it is the same Database.Please find below the sample structure

table_name (comes from Table1)
Table1--------------->Table2(select field1, field2 from table_name ) is the query where the table_name should be taken from the Table1 table.

Please suggest how to store that value and pass it in the query for the Table2 in parallel job

Thanks again

[chulett]

Please suggest how to store that value and pass it in the query for the Table2 in parallel job

He did.

[suryadev]

Yes, we can write a subquery and feed it to the main query for the table_name information I am looking for but the issue is the table_name is not a straight field which can be pulled from the sub query. I did pull it from an other table and did some transformations for that value, now that value is the table_name which has to be fed to the main query?

any other way to do it with out using two parallel jobs and putting them in a sequence ?

Thank you!

[chulett]

If you need to manipulate the value and then send it in as a job parameter then you'll need two jobs and a Sequence. And he specifically noted a Server job for the first step so you can leverage USERSTATUS which makes the transfer of data between the jobs trivial.