lookup

cosec · Post by **cosec** » Wed Jun 27, 2007 10:42 pm

Hi

(1)
Currently I have two relevant input columns attached to the Key expression columns of the lookup...and then I have a stage variable to check if they match or not and if successful to extract relevant fields from the lookup....for eg the check expression is :
If Input.Col1=Lkp.Col1 And Input.Col2=Lkp.Col2 Then 1 else 0

Is this correct ? and is there a better way to do it ?

(2)
My Lookup doesnt have any matches but I get a warning as follows:
Reference lookup generated 82 rows. Only the last row was used.
Do I need to check on anything ?

Thanks

kumar_s · Post by **kumar_s** » Wed Jun 27, 2007 10:49 pm

There should be a match, and there is duplicate in lookup. Hence the warning.
NOTFOUND option could also be used as alternative.

cosec · Post by **cosec** » Wed Jun 27, 2007 11:11 pm

I verified the lookup data and there is no match.......also is my Q(1) correct ?

ArndW · Post by **ArndW** » Wed Jun 27, 2007 11:23 pm

cosec - how did you verify this? Since DataStage found 82 matches on the key you supplied, yet you are certain there are not matches you have eitehr got a problem with your logic or have discovered a bug in DataStage.

cosec · Post by **cosec** » Thu Jun 28, 2007 12:30 am

I created the lookup table with no match. Anyway when I chose the "generate sql" option it seemed to work right.....but doesnt for"Enter SQL"

I am doing a lookup on the database and not hash file.....by the way is it ok to do a lookup on the database it self without hashing ??

ArndW wrote:cosec - how did you verify this? Since DataStage found 82 matches on the key you supplied, yet you are certain there are not matches you have eitehr got a problem with your logic or have discovered a bug in DataStage.

ArndW · Post by **ArndW** » Thu Jun 28, 2007 12:45 am

What happens when you manually enter the SQL with the appropriate key inside your favorite SQL query tool - do you get no matches or 82?

You can lookup on the database and hashing doesn't apply in this context in server jobs (is this a server job?)

cosec · Post by **cosec** » Thu Jun 28, 2007 3:35 am

No Matches....OK thanks

ArndW wrote:What happens when you manually enter the SQL with the appropriate key inside your favorite SQL query tool - do you get no matches or 82?

You can lookup on the database and hashing doesn't apply in this context in server jobs (is this a server job?)

ray.wurlod · Post by **ray.wurlod** » Thu Jun 28, 2007 4:09 am

Your constraint expression is not correct. If the lookup fails, columns from Lkp will return NULL. Using NULL in a comparison is not defined. A better constraint expression would be Not(IsNull(Lkp.Col1)).
You don't need to check Col2, and you don't need to check that the returned values are the same as the stream input values - they must be if these are the reference keys.