DSXchange

Hi, hope everybody is doing well. Need your help.

I am trying to load a datastage server job which has one look up table.
It structures this way,
Address look up------
Input ---------Transformer------Output

Now, there is a column "street address" which populates data Add1(varchar,100) and Add2(varchar,70)from "Address look up table".
I used a concatenate function in transformer saying " Streetaddress = add1+ " "+add2. The output Street address column data type is Varchar,256 which is linked to Address Look up table.

When I use concatenate of above , it is not adding anything keeping the column null. When I join "add1", it is working fine adding only "add1" column. Not sure why it is not adding all two columns,, Any help

Thanks,

It's standard NULL handling, once you introduce NULL into any operation, the end result is NULL. Assuming only the second field can be NULL, wrap it in a NullToEmpty() transform during the concatenation.

Thanks Craig. I actually can't see the whole explanation as it is locked though I have premium membership. I have sent an email to the editor, hoping it will be solved very soon.

Now, NullToEmpty returns an empty string if the input column is null, otherwise it returns the input column value.right

The issue is that, Add1 suppose :272, Ohio Street, and Add2 is "Suite 200.

So when I concatenate,it should be "272,Ohio St Suite200. In many location code which I am joining with input location code, add2 has value, some of them add2 does not have value.

I assume you mean the concatenated value you require in the example is:
"272, Ohio Street, Suite 200"

in which case you would concatenate Add1, followed by a space followed by NullToEmpty(Add2)

After that I do not understand what the issue is.
Are you using the result of this concatenation to match to values in a lookup?
If Add2 is empty the result in the above example would be "272, Ohio Street, " though you would presumably not append the space in this case.
Is the issue that when the 2nd field is empty it doesn't match any value in the reference data lookup? If that's the case you'll need to be more specific about the requirement in these circumstances.

Thanks a lot.. I got it. Its working fine now. I was keep using + while concatenating the statement. So, the final output was 0. When used : instead of +, it worked. Thanks again.

Sorry, I should have said something... noticed the strange "+" syntax but figured you were posting "psuedo-code" rather than what you were actually doing (math) especially when you said the end result was NULL in the first post rather than zero. My advice still stands, however, if you need to concatentate strings together and any of them can be NULL.

Don't forget you can also do conditional statements inside the concatenation operators when needed:
Should be close to correct. Very similar to what the NullToEmpty does under the covers but only adds the space when the second address exists.

No problem Craig. Thanks a lot for your help. Appreciate it as always.
I am having one more issue, have not able to figure out whats wrong in it. I am posting in a different topic. Thanks.