On 24 Apr 2012 07:24, Jeff Young wrote:
Is it possible within the WHEN clause of the CASE statement to have
multiple operations?
I have a condition where I need to get both the count and total
amount for selected transaction types into separate variables by
transaction type.
I do not want to use a cursor to summarize by type.
Transaction File
Type 2A
Amt 11 2
Type can be 'IN', 'CM', 'PA', 'DM'
I want to get the total transactions and amount by type.
Ignoring what a CASE could or would not be able to effect...
The result, as I infer from the described, could be made from a set
of scalar fullselects to make a row-fullselect, without any CASE
clauses; e.g.:
create table transact (type char(2), amt dec(11, 2))
;
select
( select dec( sum(Amt), 15, 2 ) from transact where Type='IN' )
,( select dec( count(*), 7 ) from transact where Type='IN' )
,( select dec( sum(Amt), 15, 2 ) from transact where Type='CM' )
,( select dec( count(*), 7 ) from transact where Type='CM' )
,( select dec( sum(Amt), 15, 2 ) from transact where Type='PA' )
,( select dec( count(*), 7 ) from transact where Type='PA' )
,( select dec( sum(Amt), 15, 2 ) from transact where Type='DM' )
,( select dec( count(*), 7 ) from transact where Type='DM' )
into :SumINvals :i , :CntINvals , :SumCMvals :i , :CntCMvals
, :SumPAvals :i , :CntPAvals , :SumDMvals :i , :CntDMvals
from sysibm/sysdummy1
-- indicator variable :i debased; reused for all SUMs, so...
-- verify CntXXvals>0 before reference to any SumXXvals,
-- or ifnull the sum result, or use separate indicators
I am not sure if a VALUES clause as a row-fullselect for a VALUES
statement using the VALUES ... INTO syntax [or for a SET statement]
could be used to eliminate the select on a dummy table; e.g. on releases
[6.1+] where that may be supported.?
A "pivot" query may be generally better, effecting just a single
query\open on the TABLE, but there still may be advantages to the above
use of separate queries. The above could be done even better instead,
as one static statement repeated for each Type, performed in a loop,
setting array elements or similar contiguous storage\variables on the
successive passes for the specified Type; e.g. making a reusable query
with the host variable on the WHERE clause, something like:
do for i=1 to %elem(Types);
execsql
select ifnull(sum(Amt),0), count(*)
into :Types(i).Sum , :Types(i).Cnt
from transact
where Type=:Types(i).Type
;
enddo;
Regards, Chuck
midrange.com
