CONCATENATING THE COLUMNS
Moderators: chulett, rschirm, roy
CONCATENATING THE COLUMNS
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
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
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
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.
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.
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.