RE: Stored Procedure with different SUMs

1)
SET P_LASTYEARYEAREND = DATE(RTRIM(CHAR(YEAR(P_CURRENTMONTH - 1 YEAR)))
CONCAT '-10-31');
SET P_TWOYEARSAGOYEAREND = DATE(RTRIM(CHAR(YEAR(P_CURRENTMONTH - 2 YEARS)))
CONCAT '-10-31');
2)
You said ORDER BY, but I assume you meant GROUP BY. You cannot exclude GROUP
BY columns if you want to project them (include them in the selected
columns).
One hack you might be able to use is to use an aggregate function as an
output, i.e. MAX(MFLIB.SLSMAS.SLSNM) , MIN(AMFLIB.CUSMAS.CUSNM) ,
AVG(AMFLIB.MBB0REP.B0GTTX), but that often doesn't make sense in the context
of the query.
I suppose you could run separate queries for these and store them in local
variables, then include them in the query output.
3)
You can try SET SCHEMA AMFLIB, but often these values are set as connection
strings or in a library list (if using *SYS naming convention).
4)
You can UNIONize different queries in a single statement.
That said, you can have more than one SUM clause in your statement. For
example, you can have 3 SUMs for 3 different years, i.e.

SUM(CASE WHEN thisYear THEN JQGLVA ELSE 0 END) AS "This year booking
value",
SUM(CASE WHEN lastYear THEN JQGLVA ELSE 0 END) AS "Last year booking
value",
SUM(CASE WHEN twoYearsAgo THEN JQGLVA ELSE 0 END) AS "Two years ago booking
value"

5)
you don't need the SET RESULT SETS CURSOR C1 statement. OPEN C1 will
suffice.

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Stored Procedure with different SUMs

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