Inconsistet behaviour in Transformation functions in DS-8.5

[Aquilis]

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.

[ray.wurlod]

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?)

[Aquilis]

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)

[BI-RMA]

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?

[srinivas.nettalam]

for point 2:
Lookup defaults a string output column to 'Empty',numeric to 0(Zero) and date/timestamp to '*****'(not sure of number of stars) if output is not nullable and there is not match on the keys.