Reference lookup generated, Only the last row was used

[shilpa79]

IndividualDim_S..X_individual: Reference lookup generated 3 rows. Only the last row was used.
When data exceeds above the recordcount then I am getting this warning.

[narasimha]

You need to change your lookup logic so that only a single row is returned

Or are you trying to return multiple row for lookup ?

Give your job design, what you are trying to do, more details.... if the above does not help you.

[shilpa79]

I am applying type II for this Job design:
When I am loading initially it will run successfully without any warnings.
when I run with the diff month of data with update and inserts i am getting the above error.

[chulett]

What stage are you using to do the reference lookup? As noted, when your query in the stage returns more than one record, only one will be returned. Some stages do this silently, some do it and log a warning as well. The solution is to rewrite the query so that it returns the singleton row you want out of the rows it selects.

Unless you need to return multiple rows, for that you'd have to use either the ODBC or UV stage.

[kris007]

Since you have mentioned that you are applying type2 for this job, I some how have a feeling that you are not selecting your keys correctly. I might be wrong too. Also, what stage are you using for lookups?

[shilpa79]

I am using DB2 udb stage as lookup stage and inputstage

[chulett]

Ok... what about everything else? As noted, it can only return a single row when used as a lookup so you'll need to correct your query in order to make the message that brought you here go away.

[neeraj]

Shilpa,

What I assume your table have some duplicates.. The result when writing to hasfile. It takes the last record and update.

So remove the duplicates from the table.

Regards
Neeraj

[tagnihotri]

Agree with Craig, multiple rows returned never sounds good.

Ok... what about everything else? As noted, it can only return a single row when used as a lookup so you'll need to correct your query in order to make the message that brought you here go away.

[ray.wurlod]

Sounds like you are populating your reference data set (from the dimension table) with both current and non-current rows. Check that you only load current rows.

[shilpa79]

The bussiness want to maintain the multiple records(Realtime) for each employee working on different locations and SSN in the lookup and insert a single active record in the target table.
So that when the same employee come in with the same SSN and same ID and diff location it will do the updates and inserts.

[tagnihotri]

As highlighted "insert a single active record in the target table", we should have only one active record which if changed should be updated or if a new record not present in db comes, should be inserted.

Therefore in no scenario the count of records returned from the lookup should be more than one, if you are extracting only active records!