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

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.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 on our policy page. If you have questions about this, please contact [javascript protected email address].