DSXchange

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

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

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

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.

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.

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?)

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?)

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.