concatenate two fields in Transformer
Moderators: chulett, rschirm, roy
concatenate two fields in Transformer
Hi,
I am selecting two fields from Oracle 10G database using ODBC stage and trying to concatenate them in transformer stage using ":".
Issue is output field is populated only if both fields have values in it.
One of the field is empty for some records.
Please let me know what is wrong.
Thanks in advance.
I am selecting two fields from Oracle 10G database using ODBC stage and trying to concatenate them in transformer stage using ":".
Issue is output field is populated only if both fields have values in it.
One of the field is empty for some records.
Please let me know what is wrong.
Thanks in advance.
Thanks Rob,
Sorry for not mentioning it before.
We are moving from SQL server to Oracle, This job is working file while reading the same fields and concatenation. This issue came up while reading the data from Oracle.
We used concatenate in many jobs, I am looking for a easy solution, so that I don't have to change all jobs.
Thanks.
Poorna
Sorry for not mentioning it before.
We are moving from SQL server to Oracle, This job is working file while reading the same fields and concatenation. This issue came up while reading the data from Oracle.
We used concatenate in many jobs, I am looking for a easy solution, so that I don't have to change all jobs.
Thanks.
Poorna
I dont think there is an easy solution. You cannot concatenate null to another string and expect to get the other string. It will always give you null.
You will have to change your jobs.
The other easy way that I can see is to update your source to a space whereever a null is present. But even for that you will have to do it within your job as your dba's wont allow you to do a mass update of all null fields and rightfully so.
You will have to change your jobs.
The other easy way that I can see is to update your source to a space whereever a null is present. But even for that you will have to do it within your job as your dba's wont allow you to do a mass update of all null fields and rightfully so.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
A difference in what SQL Server selected versus what you are getting from Oracle. Sounds like you got an empty string before and now you are getting nulls. As noted, there is no magic bullet or easy answer here. You have to change all the jobs.
You'll either need to change what gets selected using a function like NVL in your queries or make use of the NullToEmpty transform in your derivations. For the latter, it's very 'cook book' - any field that could be null gets wrapped in the NullToEmpty transform. For example:
You'll either need to change what gets selected using a function like NVL in your queries or make use of the NullToEmpty transform in your derivations. For the latter, it's very 'cook book' - any field that could be null gets wrapped in the NullToEmpty transform. For example:
Code: Select all
NullToEmpty(Field1) : NullToEmpty(Field2)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Derivation Substitution
Select one of more of these derivations, then right click and choose Derivation Substitution from the menu. This allows you to edit some of all of the selected derivations. For example, if you choose the "entire expression" option and enter NullToValue($1), each expression in the selected set is replaced with NullToValue(expression). An excellent tool for developer productivity.
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.
I hesitate to mention this as I don't think it is a good idea but if you really, really didn't want to alter the guts of the DataStage jobs you could overlay the Oracle tables with views that did the NULL handling and anything else that makes the Oracle source behave differently then a SQL Server source on a field by field basis.
I'd bite the magic bullet and change the jobs however.
I'd bite the magic bullet and change the jobs however.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Oh, wow, a SQL Server shell for Oracle! What a concept!
But, why not? We have UNIX shells for Windows, and DOS shells for UNIX.
Thinks... is there a niche market here?
But, why not? We have UNIX shells for Windows, and DOS shells for UNIX.
Thinks... is there a niche market here?
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.