DSXchange

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

You can reference the SQL server table itself.

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

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