CONCATENATING THE COLUMNS

[rajeevm]

Hi All,

I am facing a problem when I am using the concatenation operator . As my job has


DB2STAGE --------> TRANSFORMER -----------> DB2STAGE

and I have 6 Hash Files for Look Up and in my Target I have one column X in which I have to have all the hash files fields into this column i.e it should appear in one statement . If I use concatenation operator : but nothing is returned and all that column has only NULL's in it. What is the solution to this problem becasue I have data in my Hash files but somehow it is not turning up.


I am giving here what I gave to concatenate all the hash file fields:

[b]'SR:':SR_LookUp.REF_VALUE:',': 'CR:':CR_LookUp.REF_VALUE:',':'1NR:': INR_LookUp.REF_VALUE:',':'CCR:': CCR_LookUp.REF_VALUE:',':'OFR:':OFR_LookUp.REF_VALUE:',':'IFR:':IFR_LookUp.REF_VALUE[/b]

Please help me out with your valuab le suggestions.

Thanks

[chulett]

If you concatenate a single NULL value anywhere in your collection of fields, then the end result will always be NULL. So, ensure that none of the fields can be NULL. I've found that the NullToEmpty transform works wonders in situations like this.

[rajeevm]

Hi chulett,


I have checked my hash files i couldn't find any of the Null values but may be when I am joining the source columns to this hash file to get the look up ,those source columns might be null

Could you please explain me in detail how this transform works so that how can i use it.

Thanks for the help.

[paddu]

NulltoEmpty should be your solution ,

I have a situation where i compare 10 columns from Hash file (lookup) to the source .i concatenate 10 columns with the synatx below for comparison .

((NulltoEmpty(Col1)):(NulltoEmpty(Col2)): ..so on)

[ray.wurlod]

Use the Debugger or stage tracing to process a few rows and convince yourself that there are nulls appearing from somewhere (a failed lookup, for example, will generate nulls in the same way that a left outer join will).

Then set up some stage variables so you can handle the various scenarios without your expression becoming unwieldy. There is an upper limit on the length of a single expression.

[rajeevm]

I don't understand how to implement this transform with my code as I have prefixes starting of the column how could i do that please explain me in detail

Thanks for the help

[rajeevm]

Hi ray,

But I don't have a failed look up but some how I am getting all the null values except 2 rows , I am getting only two rows from source to the particular target column . when I am trying to concatenate all the 6 fields from 6 different hash files.

Thanks for your help Ray if provide me with good solution.

[chulett]

Paddu gave you an example of the syntax.

[rajeevm]

Thanks a lot Paddu,

Your code worked out . Thank you to all of you guys for giving me good suggestions and help..

Thanks

Rajeev

[paddu]

No problem