I was wondering if anyone has a better way to get totals of the tfgld amount fields where all the numbers are positive.

For example, the following does not work:
SELECT tfgld106.odoc, SUM(tfgld106.amth)
FROM tfgld106
WHERE ...
GROUP BY tfgld106.odoc
...
tfgld106.amth is always positive, whether it is a debit or credit. I mean, I know it can be negative, but a credit of $10 and a debit of $10 are both expressed as 10.0000.

Instead I have to use something like the following:

temp.amnt = 0
SELECT tfgld106.odoc, tfgld106.dbcr, SUM(tfgld106.amth)
FROM tfgld106
WHERE ...
GROUP BY tfgld106.odoc, tfgld106.dbcr
SELECTDO
If tfgld106.dbcr = tfgld.dbcr.credit
temp.amnt = temp.amnt - tfgld106.amth
else
temp.amnt = temp.amnt + tfgld106.amth
endif
ENDSELECTThis always seems kind of clumsy to me and usually results in a lot more code and nested queries.

Thanks,
Rob

You could sum up the debits then sum up the credits, using two different select statements, then put them together... I don't know that this would be any faster from a performance perspective.

Vince

When u c oracle driver sucking substantial portion of server resources when this query, u relieve the driver by assigning some portion of it's work ie group by to bshell . U can do so by using arrays . In the process overall throughput will be much better.

Or more better alternative is to use order by tfgld106._index1 , which gives u records in sorted manner and u can easily sum up values in simple variables and use it when odoc changes .No need for arrays also.