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
Using Aggregate Sum function in a lookup
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The greater simplicity of the key structure, and the fact that the hashed file can be cached in memory for reading, both contribute to the performance gain you will experience. I thoroughly endorse Vincent's response.
You can load the hashed file in the same job in which you wish to use it, or in a separate job run prior.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
You can load the hashed file in the same job in which you wish to use it, or in a separate job run prior.
Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518
While you are better off taking Vincent's advise, to try and answer your question...
In custom sql, you have complete control of the 'where' clause. Heck, you write the entire sql yourself, including any needed 'where', 'order by', etc clauses. Plus there's the fact that the only way you could do a query like in your example is with a custom sql.
The only odd thing you have to do is include *all* of the columns in the custom sql, so you have to return the key values along with the summed columns.
-craig
In custom sql, you have complete control of the 'where' clause. Heck, you write the entire sql yourself, including any needed 'where', 'order by', etc clauses. Plus there's the fact that the only way you could do a query like in your example is with a custom sql.
The only odd thing you have to do is include *all* of the columns in the custom sql, so you have to return the key values along with the summed columns.
-craig