NOTFOUND link variable curious behaviour

rabs · Post by **rabs** » Wed Dec 17, 2003 2:18 am

Hi all,

I have encountered some curious behaviour regrading the link variable NOTFOUND. For one of my jobs the lookup produces no results, however the contraint which uses the lookup variable NOTFOUND still evaulates to true. When the NOTFOUND link variable is replaced with IsNull(lookup.source_column) the constraint evaulates to false (the correct behaviour). In other words, the NOTFOUND link variable is wrong.

Anyone else encounter this before?

P.S I performed a search, but most occurences of the NOTFOUND keyword in the forum were of recommendations of its use.

dhwankim · Post by **dhwankim** » Wed Dec 17, 2003 2:37 am

Maybe Your job look up OCI Stage or somelike that (External DBMS Tables) but If your job loo up the hashed file stage. NOTFOUND works Well.

peterbaun · Post by **peterbaun** » Wed Dec 17, 2003 2:40 am

Hi -

Sounds to me that the behaviour is correct.

If the lookup doesn't produce a result then NOTFOUND is true (implied in row NOT FOUND

) - so you need to negate it in order to get the result you want

eg. Not(LinkName.NOTFOUND)

Regards
Peter

rabs · Post by **rabs** » Wed Dec 17, 2003 4:33 am

peterbaun wrote:Hi -

Sounds to me that the behaviour is correct.

If the lookup doesn't produce a result then NOTFOUND is true (implied in row NOT FOUND ) - so you need to negate it in order to get the result you want

eg. Not(LinkName.NOTFOUND)

Regards
Peter

Sorry, that is what I meant :D

Code: Select all

If(IsNull(LinkName.source_column))

should be equivalent to

Code: Select all

If(LinkName.NOTFOUND)

right? But these two things are giving different results.

Btw, the lookup is against a hash file.

mhester · Post by **mhester** » Wed Dec 17, 2003 6:52 am

Sounds to me like it is working correctly. You have outlined two (2) conditions -

1) linkname.column is null

AND

2) linkname.NOTFOUND

Just because linkname.column evaluates to null does not indicate that the lookup failed.

You need to evaluate both conditions. If the lookup fails then the value of linkname.NOTFOUND = 1 and you do not need to check linkname.column.

If the value of linkname.NOTFOUND = 0 then, depending on your logic, you will need to interrogate linkname.column for a valid value.

Regards,

Michael Hester

trobinson · Post by **trobinson** » Wed Dec 17, 2003 9:08 am

I never use NOTFound for the reasons stated. Why make it more complicated then it has to be? NOT(ISNULL(lookup)) covers all the bases as far as I'm concerned. I always use NOT(ISNULL(lookup)) and as a Best Practice I self-document what the lookup is trying to achieve in a stage variable and then use the stage variable in any constraints I need. I almost have a problem with the negatvie logic of NOT(ISNULL) but then life isn't not not perfect. I think it is better than the double negative NOT(NOTFOUND).

rabs · Post by **rabs** » Wed Dec 17, 2003 9:14 am

mhester wrote:Sounds to me like it is working correctly. You have outlined two (2) conditions -

1) linkname.column is null

AND

2) linkname.NOTFOUND

Just because linkname.column evaluates to null does not indicate that the lookup failed.

You need to evaluate both conditions. If the lookup fails then the value of linkname.NOTFOUND = 1 and you do not need to check linkname.column.

If the value of linkname.NOTFOUND = 0 then, depending on your logic, you will need to interrogate linkname.column for a valid value.

Regards,

Michael Hester

Yep, agree with you. However, the hashfile being looked up against only contains one field (the key field). So if that field is null it must be linkname.notfound right?

xli · Post by **xli** » Wed Dec 17, 2003 2:09 pm

I have already noticed this issue even from DataStage version4.0 to version6.0. Also, I noticed that in most of jobs, the NOTFOUND function works. But in some jobs, even very simple jobs, for no particular reason, It produced wicked logic result.

Now, I have changed all my jobs to use ISNULL or NOT(ISULL()) instead NOTFOUND function.

I hope somebody can provide a reasonable explaination.

xiong

tonystark622 · Post by **tonystark622** » Wed Dec 17, 2003 3:47 pm

rabs,

Is there really a row in the hash file where the column is null? I assume that the keyfield in the input row is not null. Is this true? Might be a valid match. Might not.

Good luck,
Tony

ray.wurlod · Post by **ray.wurlod** » Wed Dec 17, 2003 3:49 pm

IsNull() is appropriate on the key value for the lookup (since keys must be NOT NULL) but not on non-key columns, which might return null even if their row is found.
Personally I have never had a problem with the NOTFOUND link variable, but all (well, at least 99%) of my lookups are to local hashed files or UV tables.

rabs · Post by **rabs** » Thu Dec 18, 2003 1:58 am

ray.wurlod wrote:IsNull() is appropriate on the key value for the lookup (since keys must be NOT NULL) but not on non-key columns, which might return null even if their row is found.
Personally I have never had a problem with the NOTFOUND link variable, but all (well, at least 99%) of my lookups are to local hashed files or UV tables.

Yeah I have never found it either (over a few years of use and multiple projects/versions). I guess I will just stick with the IsNull.

Tony - the IsNull is being performed on the key field, which is the only field on the hash file.

XLI - good to hear someone has encountered similar behaviour.

Interestingly, one of my colleagues said she was advised by an Ascential trainer to avoid use of NotFound because it sometimes doesn't work.

Anyway, thanks for all your help,
rabs

DSXchange

NOTFOUND link variable curious behaviour

NOTFOUND link variable curious behaviour

Re: NOTFOUND link variable curious behaviour