× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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

This mailing list archive is Copyright 1997-2024 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].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.