concatenate two fields in Transformer

poorna_76 · Post by **poorna_76** » Mon Apr 09, 2007 12:50 pm

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.

trobinson · Post by **trobinson** » Mon Apr 09, 2007 12:57 pm

By empty, I assume you mean NULL.

One cannot concatenate fields that contain NULL. "Nothing" concatenated to something is not possible. The result is "nothing". You could test for NULL and then concatenate a true empty string. That is '' (tick tick).

poorna_76 · Post by **poorna_76** » Mon Apr 09, 2007 1:19 pm

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

DSguru2B · Post by **DSguru2B** » Mon Apr 09, 2007 1:38 pm

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.

chulett · Post by **chulett** » Mon Apr 09, 2007 1:46 pm

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:

Code: Select all

NullToEmpty(Field1) : NullToEmpty(Field2)

poorna_76 · Post by **poorna_76** » Mon Apr 09, 2007 1:50 pm

Thanks Guys.

ray.wurlod · Post by **ray.wurlod** » Mon Apr 09, 2007 2:40 pm

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.

trobinson · Post by **trobinson** » Tue Apr 10, 2007 6:30 am

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.

chulett · Post by **chulett** » Tue Apr 10, 2007 6:35 am

To me... this topic is resolved. You've gotten the answer not a 'workaround', even if it's not the one you were hoping for.

But perhaps that's just me.

ray.wurlod · Post by **ray.wurlod** » Tue Apr 10, 2007 1:09 pm

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?