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

Thanks guys.

I never really understood the 'black magic' behind queries, but now I see that from the query's point of view it is always looking at a single record and aggregates based on the FROM criteria. Thanks again.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: Thursday, July 02, 2009 3:20 PM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs

I think Elvis means...

select COMNO, CUSNO, SLSNO, ITCLS,
sum( case when someDate between xxx and xxxx then JQGLVA
else 0) as curMth,
sum( case when someDate between xxx and xxxx then JQGLVA
else 0) as lastYear,
sum( case when someDate between xxx and xxxx then JQGLVA
else 0) as twoYears
from <...>
group by COMNO, CUSNO, SLSNO, ITCLS

Nice technique Elvis! I learned something new ( or at least got a refresher ;)

That's probably going to be the OPs best solution.

Charles

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

Thanks for responding.

I was able to get this to work as well:
SET P_LASTYEARYEAREND = DATE ( CHAR ( ') || CHAR(YEAR(P_CURRENTMONTH - 1
YEAR)) || "-10-31" || CHAR(' ) ) ;
SET P_TWOYEARSAGOYEAREND = DATE ( CHAR ( ') || CHAR(YEAR(P_CURRENTMONTH
- 1 YEAR)) || "-10-31" || CHAR(' ) ) ;

Regarding #4, I don't quite get your response. How will a CASE replace
a SELECT subquery?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Thursday, July 02, 2009 2:24 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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

--
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.

--
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.