DSXchange

I have a question on lookup.

For both Source and lookup, I have Oracle Enterprise stage and after lookup, a transformer is there. In the transformer there is code: IF NUM1>0 THEN 'Y' ELSE 'N'

NUM1 is coming from lookup stage and is is Char / Not Null.

My question is about what happens when the data mismatches with look up. I know it will be spaces but when we can compare to a numeric field > 0 it was displaying always Y' only, independent of whether it matches or not.

[Note - grammar corrected to clarify question]

First of all, if a lookup fails, it is going to return NULLs for the returned columns (assuming the lookup is set to Continue after a failure), not spaces. To handle this correctly the columns that are returned all need to be set to "nullable".

Try setting column definition to nullable and comparing after a trim and null handling. Since it is a CHAR field, it will be padded to max length.

Something like:

If Trim(NullToValue(inputcol.NUM1),"") > 0

This way, if it is NULL, it returns empty string. Otherwise the contents are trimmed and converted to integer for the comparison.

Note: You should be 100% sure that NUM1 will always contain a padded integer. Otherwise use IsValid function to test and insure the trimmed value will be a valid integer.

asorrell.....Thanks !!

Well, here I want to know functionality how it works.

At look-up stage options are continue , continue... So it will not fail now.

When I am doing IF NUM1>0 THEN 'Y' ELSE 'N' at Xtrm....how does Datastage validates.....

When mismatches NUM1 will be spaces....then space >0 will always be 'Y' ?

Yes - if it is spaces it will always be "Y". Since it can't do a conversion of the spaces to a valid integer, it does an character comparison, so what you get is which is apparently "Y".

@asorrell .......

what it was doing comparing char with numeric....... how can it be true ?

Though that is what you are requesting by comparing a char against a numeric (Is CharField > 0), it can't do that. It has to either convert them both to numbers and compare or convert them both to strings and compare. Since " " doesn't convert to a valid number, it is comparing the multi-byte char field with the string representation of a zero.

DataStage has "intrinsic" conversions built in as part of its historical support for the old "Server" version. In the older server product everything (and I mean EVERYTHING) was stored as string. If you referenced it like a number (did math with it) and it could be converted to a valid number, it was converted and the math was done.

DataStage parallel transformers support intrinsic conversions from string to integer and vice versa - no function required.

If Trim(NullToEmpty(inputcol.NUM1)) > 0 THEN 'Y' ELSE 'N'

Thanks everyone for your patience. Looks like my question is misleading everyone.

I am not trying to fix or coding the logic, someone else developed the job and trying to know how it will work.

As you said: Since " " doesn't convert to a valid number, it is comparing the multi-byte char field with the string representation of a zero.

When I am doing IF NUM1>0 THEN 'Y' ELSE 'N' in transformer how does DataStage validate? It was developed in parallel version.

Here my actual question was when we compare string with number, to my knowledge, it should be 'N'. I don't understand, how Parallel version will always pick 'Y'. Even when I tested one parallel job....its always picking 'Y only.

I will re-iterate. Since the char field doesn't convert to a valid number, DataStage is converting the zero to a string "0" and doing the comparison. At that point it is not a numeric comparison, it is an alpha comparison.

And since it is comparing a padded string (char field NUM1) of five characters to a single character ("0"), NUM1 is ALWAYS greater than the zero.

Now to address the real issue, this is probably wrong. What the developer probably was trying to do was see if the number in the field was greater than zero. If that is the case, then it needs to be modified to either of the two examples given above, with trims and null-handling.