Joining / doing lookup without a key
Moderators: chulett, rschirm, roy
Joining / doing lookup without a key
Hi,
We have a requirment where we need to bring a value into the primary flow of data from a reference table without matching on a key.
The source is a sequential file. We just need to add an additional column from a reference table into this stream. There is no column on which we could do a match to fetch that reference column. The reference table has only one row and that single value has to be brought into all the rows of the actual stream. We tried lookup stage and lookup stage didn't allow to bring a field from a refernce link without doing a match on a key.
Could anyone please suggest a method to have this done.
Thank you.
We have a requirment where we need to bring a value into the primary flow of data from a reference table without matching on a key.
The source is a sequential file. We just need to add an additional column from a reference table into this stream. There is no column on which we could do a match to fetch that reference column. The reference table has only one row and that single value has to be brought into all the rows of the actual stream. We tried lookup stage and lookup stage didn't allow to bring a field from a refernce link without doing a match on a key.
Could anyone please suggest a method to have this done.
Thank you.
-
- Participant
- Posts: 78
- Joined: Fri Mar 02, 2007 4:54 am
- Location: CHENNAI
No, that cannot be done. The column we fetch from the Reference table is an ID column. So, the value would be different / change when migrated to different enviornments.
Also, we have to do a similar kind of ID column fetching from different reference tables in different jobs. So, if we hard code in all places, it becomes a too much of hard coding in the overall project. So, we wanted to avoid such a thing.
Also, we have to do a similar kind of ID column fetching from different reference tables in different jobs. So, if we hard code in all places, it becomes a too much of hard coding in the overall project. So, we wanted to avoid such a thing.
i.e., Reading the value from the table and passing it as parameter to the job and substitute in Transformer for the specific fields.
By this way, you are avoiding lookup for each record (Incase you do a sparse lookup).
By this way, you are avoiding lookup for each record (Incase you do a sparse lookup).
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
Since the Parameter data is very few, you can use a Server job to read the data from Table, and using transformer, UtilityRunJob can be used to call the job and pass the extracted value to the job as parameter.
Alternatively you can Access the data from the table and store it in a file and then read the file via ExecuteComand Activity or any script to pass it.
But the earlier could be simpler.
Alternatively you can Access the data from the table and store it in a file and then read the file via ExecuteComand Activity or any script to pass it.
But the earlier could be simpler.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
I would like to clarify on what you are saying. You mean to have a separate Server job to run the SQL that fetches the required value. And then call this job from the second job to get the output of that SQL.kumar_s wrote:Since the Parameter data is very few, you can use a Server job to read the data from Table, and using transformer, UtilityRunJob can be used to call the job and pass the extracted value to the job as ...
In that case, in the first Server job, should I use an Oracle stage to execute the SQL or by some other means.
In the second job, I see that the "UtilityRunJob" cannot be used from a Parallel job Transformer.