I'd like to know if anyone has a good way of getting the column name so that it can be used as data?
What I'm trying to do is...
Read from a database table (using rcp for the column definitions), then lookup to a reference table which has a keyfields of column_name and value.
So, for each field, I want to lookup to this reference table using the column_name and value.
How would you go about it?
thanks
How to get the column name with the data into a lookup
Moderators: chulett, rschirm, roy
How is Yorkshire these days?
One option you have is to use a shared container. Pass in the column_name and value as parameters, then create those column names in a modify stage and you can use them from there on. The trick is in the modify stage, where you can use a parameter value as your column name declaration.
One option you have is to use a shared container. Pass in the column_name and value as parameters, then create those column names in a modify stage and you can use them from there on. The trick is in the modify stage, where you can use a parameter value as your column name declaration.
</a>Still wet and windy here in Yorkshire - but I'm off to sunny Liverpool-on-sea for the weekend. :D
I was hoping that I could write a generic job that would work out the column names itself from the information that the db2 stage gets from db2 - and that way I wouldn't have to worry about passing in the column names myself. Is that possible/practical?
I'm not sure I've understood your solution yet (I'm about to have a play about with it) - but I think that what you've suggested would let me set up my column name based on some data - but what I want is to set up some data based on the column name.
Here's an example of what I mean....
I've got a source table which stores a code which relates to a customers title.
Table1 - source_table
Title=1
The description of what this code means is stored in this lookup table.
Table2 - lookup_table
ColName='Title'
ColValue='1'
Description='Mr'
I need to read Table 1 - which give me the value 1 in a field called 'Title',
and then lookup to table 2 using ColName='Title' and ColValue='1' to get the Desription ('Mr').
So my problem is how to get the column name 'Title' into a field that I can use to perform the lookup.(and preferably with datastage working out the column name from the schema that it gets from db2 ).
I was hoping that I could write a generic job that would work out the column names itself from the information that the db2 stage gets from db2 - and that way I wouldn't have to worry about passing in the column names myself. Is that possible/practical?
I'm not sure I've understood your solution yet (I'm about to have a play about with it) - but I think that what you've suggested would let me set up my column name based on some data - but what I want is to set up some data based on the column name.
Here's an example of what I mean....
I've got a source table which stores a code which relates to a customers title.
Table1 - source_table
Title=1
The description of what this code means is stored in this lookup table.
Table2 - lookup_table
ColName='Title'
ColValue='1'
Description='Mr'
I need to read Table 1 - which give me the value 1 in a field called 'Title',
and then lookup to table 2 using ColName='Title' and ColValue='1' to get the Desription ('Mr').
So my problem is how to get the column name 'Title' into a field that I can use to perform the lookup.(and preferably with datastage working out the column name from the schema that it gets from db2 ).