Page 1 of 1
NOTFOUND link variable curious behaviour
Posted: Wed Dec 17, 2003 2:18 am
by rabs
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.
Re: NOTFOUND link variable curious behaviour
Posted: Wed Dec 17, 2003 2:37 am
by dhwankim
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.
Posted: Wed Dec 17, 2003 2:40 am
by peterbaun
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
Posted: Wed Dec 17, 2003 4:33 am
by rabs
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
right? But these two things are giving different results.
Btw, the lookup is against a hash file.
Posted: Wed Dec 17, 2003 6:52 am
by mhester
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
Posted: Wed Dec 17, 2003 9:08 am
by trobinson
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).
Posted: Wed Dec 17, 2003 9:14 am
by rabs
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?
Posted: Wed Dec 17, 2003 2:09 pm
by xli
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
Posted: Wed Dec 17, 2003 3:47 pm
by tonystark622
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
Posted: Wed Dec 17, 2003 3:49 pm
by ray.wurlod
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.
Posted: Thu Dec 18, 2003 1:58 am
by rabs
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