I agree that case may be used to perform such tasks, but after significant testing this can be performed much, much faster, if the only calculation occurring in the query is the Sum Aggregate Function.
So, if you have a table as such,
Create Table QTEMP.SUMME (ITEM, ITEMGRP, Value) As
(Values
('AAAAAA', 'A', 100.00),
('AAAAAA', 'B', 150.00)) With Data
The statement to summary the data would look like this.
Select
ITEM,
Sum(NEWVALUE) As NEWVALUE
From
QTEMP.SUMME As A
Left Join Lateral
(Values ('A', Value),
('B', -Value)) As B (ITEMGRP, NEWVALUE)
On
A.ITEMGRP = B.ITEMGRP
Group By
ITEM
Returns a summary amount for item 'AAAAA' = -50.00.
Put both statements into VE and see which one is faster.
Jim

This mailing list archive is Copyright 1997-2015 by MIDRANGE dot COM and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available here. If you have questions about this, please contact