Hi All,
We are encounetring multiple issues with transformation functions and Look up stage behaviour. We tried multiple combinations and end up nothing fruitful. Please advise,the issues which I'm mentioning are faced by any of you people.
1. Len (TrimleadingTrailing(Col))<>0 is not being supported in Stage variables. only Len(col) <>0 works
2. Left outer Join in look up Stage returing "Empty" rather than NULL.
3. IsNull() function never work either in column derivation or in Stage variables.
Looking out eagerly for advise.
Inconsistet behaviour in Transformation functions in DS-8.5
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Define "is not supported" or "is not working" in each case. What are your expected vs actual results? Are any error messages being generated and, if so, what are they? Finally, how are your default values configured? (For example is the output from the Lookup stage in case #2 marked as not nullable?)
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.
My Bad.
Here goes the results am getting.
1. Len (TrimleadingTrailing(Col))<>0 is not being supported in Stage variables. only Len(col) <>0 works ===> Not getting compiled. Ciompilation error
2. Left outer Join in look up Stage returing "Empty" rather than NULL .
3. IsNull() function never work either in column derivation or in Stage variables. ==>
Example - If IsNull(Col) then 1 else 0, returns '0' for any values in Filed - "Col" including null value.
(Note - both 2 & 3 are not interlinked. These are being used in different Jobs)
Here goes the results am getting.
1. Len (TrimleadingTrailing(Col))<>0 is not being supported in Stage variables. only Len(col) <>0 works ===> Not getting compiled. Ciompilation error
2. Left outer Join in look up Stage returing "Empty" rather than NULL .
3. IsNull() function never work either in column derivation or in Stage variables. ==>
Example - If IsNull(Col) then 1 else 0, returns '0' for any values in Filed - "Col" including null value.
(Note - both 2 & 3 are not interlinked. These are being used in different Jobs)
Aquilis
Hi Aquilis,
1. Your code compiles without problem on my system. Of course, Col needs to be defined in the job, that is inputLink.Col or stageVariable Col.
2. What is it you mean, Left outer join, or Lookup, or both? Check Ray's advice concerning Nullability of the output column (needs to be "Yes", otherwise will be set to a default-value in Left-Join and Lookup).
3. Not in our environment (8.5) it doesn't. Again: is there a stageVariable or Parameter named Col in the job that can be confused with a column inputLink.Col?
1. Your code compiles without problem on my system. Of course, Col needs to be defined in the job, that is inputLink.Col or stageVariable Col.
2. What is it you mean, Left outer join, or Lookup, or both? Check Ray's advice concerning Nullability of the output column (needs to be "Yes", otherwise will be set to a default-value in Left-Join and Lookup).
3. Not in our environment (8.5) it doesn't. Again: is there a stageVariable or Parameter named Col in the job that can be confused with a column inputLink.Col?
"It is not the lucky ones are grateful.
There are the grateful those are happy." Francis Bacon
There are the grateful those are happy." Francis Bacon
-
- Participant
- Posts: 134
- Joined: Tue Jun 15, 2010 2:10 am
- Location: Bangalore