calculation for new record based on multiple existingrecords

[harithay]

Hi all,

i have to calculate change_ in_ TNA ( field) for new incoming records based on same records that alredy exist in sql server database.

the key columns to calculate 'change_in_tna' are fund_name, fund_no

fund_name, fund_no are not key columns in sql server database.

but there exist more than one record(with fund_name, cik) in database, and there may be moe than record in source.


if i take existing database into hashed filebased on key columns fund_name, cik. i get only one record with same fund_name , fund_no.


inorder to take other records(same fund_name, fund_no) into hashed file
i don't have any other key column

but i need to compare all incoming records with already existing records with same fund_name , fund_no


i know Hashed file stage does not work in this case , is there any other stage that will work for this scenario.


Thanks in Advance

[Sainath.Srinivasan]

You can reference the SQL server table itself.

[vmcburney]

You might be able to modify multiple records on the target table for each input record by writing a custom update SQL where you set the target field to a aggregation of the input field and the target field. Eg. UPDATE MYTABLE SET FIELDA = FIELDA + :1 WHERE FIELDB = :2

[ray.wurlod]

You need joining (perhaps self-joining), grouping and possibly set functions when extracting from SQL Server.