Posted: Sun Jan 19, 2003 9:56 pm
You could simplify this lookup by running a job that creates a hash file with pre calculated summed values for all account numbers. This would simplify your DataStage lookup and should provide a huge performance improvement.
Example, if you were on SQL Server you would create a custom SQL with the following input and send this to a hash file:
select TA1.CD_COMPANY_SYSTEM as CD_COMPANY_SYSTEM,
TA1.NO_ACCOUNT AS NO_ACCOUNT,
sum(TA2.AM_CURRENT_BAL) as SUM_AM_CURRENT_BAL
FROM TVP008ACCOUNT AS TA1
JOIN TVP008ACCOUNT AS TA2 ON (TA2.NO_ACCOUNT>=TA1.NO_ACCOUNT-998
AND TA2.NO_ACCOUNT
Example, if you were on SQL Server you would create a custom SQL with the following input and send this to a hash file:
select TA1.CD_COMPANY_SYSTEM as CD_COMPANY_SYSTEM,
TA1.NO_ACCOUNT AS NO_ACCOUNT,
sum(TA2.AM_CURRENT_BAL) as SUM_AM_CURRENT_BAL
FROM TVP008ACCOUNT AS TA1
JOIN TVP008ACCOUNT AS TA2 ON (TA2.NO_ACCOUNT>=TA1.NO_ACCOUNT-998
AND TA2.NO_ACCOUNT