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
Read SQL into Datastage job
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
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...
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...
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...
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...
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
Read SQL from datastage job
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
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
-
- Participant
- Posts: 597
- Joined: Fri Apr 29, 2005 6:19 am
- Location: Singapore
Re: Read SQL from datastage job
Then Craig/Ray has given you 2 options.pavan_test wrote:There will be only 1 record in the oracle table (lets say table name is myz) whose Col D has selct query.
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.
Kandy
_________________
Try and Try again…You will succeed atlast!!
_________________
Try and Try again…You will succeed atlast!!