NOTFOUND link variable curious behaviour
Moderators: chulett, rschirm, roy
NOTFOUND link variable curious behaviour
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.
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.
like a tiger
Re: NOTFOUND link variable curious behaviour
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.
Sorry, that is what I meant :Dpeterbaun 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
Code: Select all
If(IsNull(LinkName.source_column))
Code: Select all
If(LinkName.NOTFOUND)
Btw, the lookup is against a hash file.
like a tiger
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
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
Mike Hester
mhester@petra-ps.com
mhester@petra-ps.com
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).
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?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
like a tiger
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
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
-
- Premium Member
- Posts: 483
- Joined: Thu Jun 12, 2003 4:47 pm
- Location: St. Louis, Missouri USA
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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.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.
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
like a tiger