Re: Stored Procedure with different SUMs -- MIDRANGE-L

I don't see anything obviously wrong with the dates, but that should
be easy enough for you to check.

I assume you mean GROUP BY not ORDER BY, and the answer is you can't.
When using a aggregate function, such as SUM(), any column on the
select not appearing in an aggregate function must be on the group by.
When you think about it, it makes sense. You want a sum of "book
values" by "Company Number", "Customer Number" and "Salesman Number"
so "Company Number", "Customer Number" and "Salesman Number" have to
be specified for GROUP BY.

As far as summing over different date ranges, a single select is not
going to do it. You mention using a subselect, that'd be one option.

select COMNO, CUSNO, SLSNO, ITCLS,
(select sum(JQGLVA) from <...> b where a.xxx = b.xxx and
somedate between xxxx and xxx) as cur_mth,
(select sum(JQGLVA) from <...> b where a.xxx = b.xxx and
somedate between xxxx and xxx) as last_year,
(select sum(JQGLVA) from <...> b where a.xxx = b.xxx and
somedate between xxxx and xxx) as twoyr_ago
from <...> a

But it seems to me your joins might make that difficult.

You might want to look at Common Table Expressions (CTE)

with cur_mth as (select COMNO, CUSNO, SLSNO, ITCLS, sum(JQGLVA) as BookValue
from <....>
where somedate between xxxx and xxx
group by COMNO, CUSNO, SLSNO, ITCLS)
, last_year as (select COMNO, CUSNO, SLSNO, ITCLS, sum(JQGLVA) as BookValue
from <....>
where somedate between xxxx and xxx
group by COMNO, CUSNO, SLSNO, ITCLS)
,twoyr_ago as (select COMNO, CUSNO, SLSNO, ITCLS, sum(JQGLVA) as BookValue
from <....>
where somedate between xxxx and xxx
group by COMNO, CUSNO, SLSNO, ITCLS)
select A.COMNO, A.CUSNO, A.SLSNO, A.ITCLS,
A.BookValue as Current, B.BookValue as LastYear, C.BookValue as TwoYears
from cur_mth A
join last_year using (COMNO, CUSNO, SLSNO, ITCLS)
join twoyr_ago using (COMNO, CUSNO, SLSNO, ITCLS)

Note that the above assumes that COMNO, CUSNO, SLSNO, ITCLS exists for
all the periods. Which is probably not a valid assumption. If the
assumption is indeed not valid, you'll have to do something like so:

with tbl as (select distinct COMNO, CUSNO, SLSNO, ITCLS from <....>)
, cur_mth as (select COMNO, CUSNO, SLSNO, ITCLS, sum(JQGLVA) as BookValue
from <....>
where somedate between xxxx and xxx
group by COMNO, CUSNO, SLSNO, ITCLS)
, last_year as (select COMNO, CUSNO, SLSNO, ITCLS, sum(JQGLVA) as BookValue
from <....>
where somedate between xxxx and xxx
group by COMNO, CUSNO, SLSNO, ITCLS)
,twoyr_ago as (select COMNO, CUSNO, SLSNO, ITCLS, sum(JQGLVA) as BookValue
from <....>
where somedate between xxxx and xxx
group by COMNO, CUSNO, SLSNO, ITCLS)
select D.COMNO, D.CUSNO, D.SLSNO, D.ITCLS,
coalesce(A.BookValue,0) as Current,
coalesce(B.BookValue,0) as LastYear,
coalesce(C.BookValue,0) as TwoYears
from tbl D
left outer join cur_mth A using (COMNO, CUSNO, SLSNO, ITCLS)
left outer join last_year using (COMNO, CUSNO, SLSNO, ITCLS)
left outer join twoyr_ago using (COMNO, CUSNO, SLSNO, ITCLS)

HTH,
Charles

On Thu, Jul 2, 2009 at 2:34 PM, ibm<ibm@xxxxxxxxxx> wrote:

Hello, I would like the experts' help on this one... The stored
procedure takes two parameters (CURRENTMONTH e.g. 2009/07, and TERRITORY
e.g. 100).

I would like the stored proc to calculate three different SUMs: one for
this month, one for last year end (2008/10) to this month and one for
two years ago, 2007/10 to 2008/07.

- Am I calculating and concatenating my date variables correctly?
- How do I exclude AMFLIB.SLSMAS.SLSNM , AMFLIB.CUSMAS.CUSNM ,
AMFLIB.MBB0REP.B0GTTX from the ORDER BY clause (If I try then the
procedure does not compile)?
- How can I set the current library to AMFLIB? SET PATH = AMFLIB
does not work?
- How should I pull the different SUMs based on the above
mentioned transaction dates (TDATE)? Should I use SELECT sub-queries for
each SUM?

BEGIN

DECLARE P_CURRENTMONTH AS DATE;
DECLARE P_LASTYEARCURRENTMONTH AS DATE;
DECLARE P_YEARENDMONTH AS INTEGER;
DECLARE P_LASTYEARYEAREND AS DATE;
DECLARE P_TWOYEARSAGOYEAREND AS DATE;

SET P_CURRENTMONTH = CURRENTMONTH;
SET P_LASTYEARCURRENTMONTH = P_CURRENTMONTH - 12 MONTHS;
SET P_YEARENDMONTH = 10;
SET P_LASTYEARYEAREND = DATE( ' '' || (YEAR(P_CURRENTMONTH) - 1 YEAR)
|| '-10-31' )
SET P_TWOYEARSAGOYEAREND = DATE( ' '' || (YEAR(P_CURRENTMONTH) - 2
YEARS) || '-10-31' )

DECLARE C1 CURSOR FOR
SELECT
AMFLIB . CUSMAS . COMNO AS "Company Number" , AMFLIB . CUSMAS .
CUSCL AS "Customer Class" ,
AMFLIB . CUSMAS . CUSNO AS "Customer Number" , AMFLIB . CUSMAS .
CUSNM AS "Customer Name" ,
AMFLIB . CUSMAS . SLSNO AS "Salesman Number" , AMFLIB . SLSMAS .
SLSNM AS "Salesman Name" ,
AMFLIB . MTHACTE . ITCLS AS "Prod. Class" , AMFLIB . MBB0REP .
B0GTTX AS "Product Class Description" ,
SUM ( AMFLIB . MTHACTE . JQGLVA ) AS "Booking Value" , SUM (
AMFLIB . MTHACTE . COQTY ) AS "Quantity"

FROM AMFLIB . CUSMAS
JOIN AMFLIB . MTHACTE
ON AMFLIB . CUSMAS . CUSNO = AMFLIB . MTHACTE .
CUSNO
JOIN AMFLIB . SLSMAS
ON AMFLIB . CUSMAS . SLSNO = AMFLIB . SLSMAS .
SLSNO
JOIN AMFLIB . MBB0REP
ON AMFLIB . MTHACTE . ITCLS = AMFLIB . MBB0REP .
B0GLCD
WHERE AMFLIB . MTHACTE . TERRN = TERRITORY
GROUP BY AMFLIB . CUSMAS . COMNO ,
AMFLIB . CUSMAS . TERRN ,
AMFLIB . CUSMAS . SLSNO ,
AMFLIB . CUSMAS . CUSCL ,
AMFLIB . CUSMAS . CUSNO ,
AMFLIB . MTHACTE . ITCLS ,
AMFLIB . SLSMAS . SLSNM , AMFLIB . CUSMAS .
CUSNM , AMFLIB . MBB0REP . B0GTTX
;

OPEN C1 ;
SET RESULT SETS CURSOR C1 ;
END
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.