Source and Target columns
Moderators: chulett, rschirm, roy
Source and Target columns
Hello All:
I have a source file that has 17 columns and a target file that has 25 columns.
In the transfomer I match the 17 columns into respective columns in the targets 25 columns?
How do I take care of the rest 8 columns? They show up Red in the transformer since they are not present in the source file. They need the 8 columns in the target.
Thanks.
MJ
I have a source file that has 17 columns and a target file that has 25 columns.
In the transfomer I match the 17 columns into respective columns in the targets 25 columns?
How do I take care of the rest 8 columns? They show up Red in the transformer since they are not present in the source file. They need the 8 columns in the target.
Thanks.
MJ
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
You can either link input columns to more then one output column or you can set output columns to null values by typing @NULL or 0 or " " into the derivation field for those empty columns or you can use a default value such as the current date time or you can find extra values by doing a lookup. This is more a business decision then a DataStage decision.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Consult your planning (source to target mapping or target from source mapping) document.
You DO have a plan, don't you?
Based on the business rules specified in this plan, fill in derivation expressions for each of the 25 target columns. They may be based on any combination of input column values, system variables, stage variables and all the other operands presented to you by the Expression Editor.
You DO have a plan, don't you?
Based on the business rules specified in this plan, fill in derivation expressions for each of the 25 target columns. They may be based on any combination of input column values, system variables, stage variables and all the other operands presented to you by the Expression Editor.
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.
Another solution for this?
Though The columns might be present in the Out put, you just Ignore them by not selecting corresponding data in the output stage, that means you are leaving them as null values by not loading any data.
RRCHINTALA
As Ray said look for the source to target mapping document. Why you need to have 8 extra columns in the target with nothing. Ask the data modeller and if not going to be used remove it from the target. Keeping extra unwanted columns is additional burden to the database and etl jobs.
Thanks
Thanks
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
do not give null value
do not give null value to he rest of column, it will cause problem in the future.
Hello All:
Thank you for all your responses.
I guess I had better explain why the target has more columns than the source. Well the target will be getting data from other sources and my job is to concentrate on 4 sources see that its data is extracted transformed and loaded to this target.
The thing is there is no documentation done yet (atleast not properly) and wanted advice from you guys (my gurus... :D ) and then go and approach these guys here.
I have been able to resolve this issue by plugging in @Null since these fields are set to Nullable in the table definitions. But still I will now go ahead and get it cleared.
Thanks once again, really appreciate all your guidence and help.
MJ
Thank you for all your responses.
I guess I had better explain why the target has more columns than the source. Well the target will be getting data from other sources and my job is to concentrate on 4 sources see that its data is extracted transformed and loaded to this target.
The thing is there is no documentation done yet (atleast not properly) and wanted advice from you guys (my gurus... :D ) and then go and approach these guys here.
I have been able to resolve this issue by plugging in @Null since these fields are set to Nullable in the table definitions. But still I will now go ahead and get it cleared.
Thanks once again, really appreciate all your guidence and help.
MJ
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It's perfectly OK to have more, or even fewer, columns in the target than in source.
If you don't want to load the other 8 columns in this job, you can omit them completely from the job design that inserts the rows (provided the omitted columns are equipped with suitable default values in the table definition).
In a subsequent job you can update these rows, providing values for the columns not yet loaded. You just have to be able to identify the unique key values for the rows.
If you don't want to load the other 8 columns in this job, you can omit them completely from the job design that inserts the rows (provided the omitted columns are equipped with suitable default values in the table definition).
In a subsequent job you can update these rows, providing values for the columns not yet loaded. You just have to be able to identify the unique key values for the rows.
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.