Return values from Lookup stage

learnpx · Post by **learnpx** » Wed Mar 23, 2005 11:08 am

What does a lookup stage return for Character fields for unmatched key values?

I am trying to apply IsNull() function for a character value that is being returned from a DB2 table. But it is not working.

Do we need to set any environmental variable to make the IsNull function work?

Thanks in advance

dsedi · Post by **dsedi** » Wed Mar 23, 2005 2:09 pm

For unmatched key values of Character fields, lookup stage returns NULL value. this is true only if you would select If Not Found property as CONTINUE in stage properties.

IsNull() function should work for charecter fields.do some cross checks..otherwise try isNull(input)=@true or simply use len(input)=0.

Edi

roy · Post by **roy** » Thu Mar 24, 2005 3:29 am

Hi,
a good practice IMHO would be also to:

build a test case and check it out,
write the results to a sequntial file and examine what you got in each case.

you might want to cross reference the results you got with any documentation you might find.

IHTH,

gh_amitava · Post by **gh_amitava** » Fri Mar 25, 2005 1:12 pm

LearnPX,

IsNull() function should work on character field. Sometime it may not work on number field. Then we need to sue NullToZero() function. Also sometime IsNull() = 0 does not work. So better to use IsNull()=1 or IsNotNull() = 1.

Regards
~Amitava

T42 · Post by **T42** » Fri Mar 25, 2005 10:08 pm

1. APT_STRING_PADCHAR plays a very important part on this.

2. If you want to use NULL as a value to identified failed matches, you need to ensure that BOTH input and reference link fields are Nullable, along with the output link field for the lookup. Yes, you do need to set the 'Continue' option as mentioned earlier.

3. If you can not be nullable, or you are directly pulling from the database which is not nullable, then APT_STRING_PADCHAR is used to fill in the 'null' field. The null flag is NOT set in this case, even if the output link is nullable.