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

Thanks, that does really help me to understand what is going on behind the
covers.

So the only overhead of declaring 100's of cursors is a one off compile time
hit, and may be a little extra disk space used for the metadata *PGM
objects.

That could be a trick to keep in mind for the future!!

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: 31 July 2009 19:45
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs - STATIC SQL

Neil,

Declaring a cursor has an impact, but for a static cursor, the CPU
impact is at compile time. Simple confirmation, here's some source:
h ACTGRP(*NEW) DFTACTGRP(*no) BNDDIR('QC2LE')

d LocInfo e ds extname(LOCATP) qualified
d myInt s 10i 0

/free
exec SQL
declare C1 cursor for
(select * from LOCATP);

myInt = 1;

exec SQL
open C1;

myInt = 2;

exec SQL
fetch next from C1 into :locInfo;

*INLR = *ON;
return;
/end-free

Now, look at the RPGLE compiler listing
77 /free
78 //**** SQL
79 //****clare C1 cursor for
80 //****(select * from LOCATP);
81
82 myInt = 1;
83
84 //**** SQL
85 //****en C1;
86 /END-FREE
87 C Z-ADD -4 SQLER6
88 C SQL_00002 IFEQ 0
89 C SQL_00003 ORNE *LOVAL
90 C CALL SQLROUTE
91 C PARM SQLCA
92 C PARM SQL_00000
93 C ELSE
94 C CALL SQLOPEN
95 C PARM SQLCA
96 C PARM SQL_00000
97 C END
98 /FREE
99
100 myInt = 2;
101
102 //**** SQL
103 //****tch next from C1 into :locInfo;
104 /END-FREE
105 C Z-ADD -4 SQLER6
106 C CALL SQLROUTE
107 C PARM SQLCA
108 C PARM SQL_00006
109 C SQL_00009 IFEQ '1'
110 C EVAL LOCINFO.LOCNBR = SQL_00011
111 C EVAL LOCINFO.LOCDSC = SQL_00012
112 C EVAL LOCINFO.GEOCDE = SQL_00013
113 C EVAL LOCINFO.UPDPRC = SQL_00014
114 C EVAL LOCINFO.LOCADD = SQL_00015

Notice the pre-compiler replaces the OPEN and FETCH with a call to
SQLROUTE/SQLOPEN. But the DECLARE isn't replaced by anything! (The
myInt assignment is just there to break up the SQL statements)

For a static statement, the meta data concerning which files and
columns are being accessed including initial plans about how to access
them, are stored in the *PGM/*SRVPGM object. In fact, this meta-data
may be updated automatically by the system after a run. (Which
originally caused a problem for auditing as the "last change date" on
the object was being updated!)

To answer your final question, the CQE/SQE gets involved during
compile time (to process the DECLARE) and again at run time to handle
the OPEN.

Now lets consider dynamic SQL
/free
exec SQL declare C1 cursor for S1 // compile time process, no
real work done

exec SQL prepare S1 from :mySQLstatement; //run time process,
lots of work done.

exec SQL open C1; //run time process, some work done.

exec SQL fetch next from C1 into :myDS; //run time, some work done.
/end free

HTH,
Charles

On Fri, Jul 31, 2009 at 10:34 AM, Neill Harper<neill.harper@xxxxxxxx> wrote:

Is that true?, I'm not countering it as I don't know... but I am

questioning

it.

It seems odd to me to suggest that declaring a cursor does not have any
impact at all, does the system not use any resources at all when a cursor

declared, i.e an object or structure that holds its definition? How does

the

cursor know what to be when it is opened?

When does the cqe /sqe get involved at cursor declare or cursor open?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
Sent: 31 July 2009 15:16
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs - STATIC SQL

Note, you're not "creating" two cursors. You've declared two, but a
cursor doesn't exist till it is opened.

With static SQL, most (all?) of the processing required when defining
a cursor occurs when the program is compiled.

So from a runtime standpoint, it doesn't matter if you've statically
declared 1 or a hundred cursors, as long as you only open one.

as far as doing it in one statement, that depends on the statements.

Charles

On Fri, Jul 31, 2009 at 9:06 AM, ibm<ibm@xxxxxxxxxx> wrote:

How can I declare different SELECT statements for a single cursor based

some criteria. Currently I am doing this:

DECLARE C1 CURSOR FOR
SELECT ... some stmt

DECLARE C2 CURSOR FOR
SELECT .... some stmt

Then, based on in input parameter I do this:
IF SELECTION_TERRITORY > 0 THEN
OPEN C1 ;
ELSE
OPEN C2 ;
END IF ;

Is there a performance penalty for creating two cursors? Could this be

done with one cursor with static SQL?

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of ibm

Sent: Tuesday, July 07, 2009 10:35 AM
To: Midrange Systems Technical Discussion
Subject: RE: Stored Procedure with different SUMs - DYNAMIC SQL SOLVED

Ok, here is the DYNAMIC SQL version of the procedure. Thanks for all the

help. Now I'll try to get the STATIC SQL working...

parameters:
SELECTION_TERRITORY DECIMAL(2,0) IN
SELECTION_YEAR INTEGER IN
SELECTION_MONTH INTEGER IN

BEGIN

DECLARE P_CURRENTYEARENDYEAR INTEGER ;
DECLARE P_YEARENDMONTH INTEGER ;
DECLARE P_YEARENDDAY INTEGER ;
DECLARE P_CURRENTMONTHDATE DATE ;
DECLARE P_CURRENTYEARENDDATE DATE ;
DECLARE P_CURRENTMONTHDATESTART DECIMAL ( 7 , 0 ) ;
DECLARE P_CURRENTMONTHDATEEND DECIMAL ( 7 , 0 ) ;
DECLARE P_CURRENTFISCALYTDDATESTART DECIMAL ( 7 , 0 ) ;
DECLARE P_CURRENTFISCALYTDDATEEND DECIMAL ( 7 , 0 ) ;
DECLARE P_LASTFISCALYTDDATESTART DECIMAL ( 7 , 0 ) ;
DECLARE P_LASTFISCALYTDDATEEND DECIMAL ( 7 , 0 ) ;

DECLARE SQLSTMT CHAR ( 2048 ) ;
DECLARE C2 CURSOR FOR S2 ;

SET P_CURRENTMONTHDATE = DATE ( RTRIM ( CHAR ( SELECTION_YEAR ) ) CONCAT

'-' CONCAT RTRIM ( CHAR ( SELECTION_MONTH ) ) CONCAT '-01' ) ;

SET P_YEARENDMONTH = 09 ;
SET P_YEARENDDAY = 30 ;
IF MONTH ( P_CURRENTMONTHDATE ) < P_YEARENDMONTH THEN
SET P_CURRENTYEARENDYEAR = SELECTION_YEAR - 1 ;
ELSE
SET P_CURRENTYEARENDYEAR = SELECTION_YEAR ;
END IF ;
--2009/09/30
SET P_CURRENTYEARENDDATE = DATE ( RTRIM ( CHAR ( P_CURRENTYEARENDYEAR ) )

CONCAT '-' CONCAT RTRIM ( CHAR ( P_YEARENDMONTH ) ) CONCAT '-' CONCAT

RTRIM

( CHAR ( P_YEARENDDAY ) ) ) ;

--2009/03/01
--2009/12/01
SET P_CURRENTMONTHDATESTART = ( YEAR ( P_CURRENTMONTHDATE ) - 1900 ) *

10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + DAY ( P_CURRENTMONTHDATE

)

;

--2009/03/31
--2009/12/31
SET P_CURRENTMONTHDATEEND = ( YEAR ( P_CURRENTMONTHDATE ) - 1900 ) *

10000

+ ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + ( DAY ( P_CURRENTMONTHDATE + 1
MONTH - 1 DAY ) - DAY ( P_CURRENTMONTHDATE ) ) ;

--DATEADD(date, 'unit', #units)
--2008/10/01
--2009/10/01
SET P_CURRENTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE ) - 1900

)

* 10000 + ( P_YEARENDMONTH * 100 ) + P_YEARENDDAY ;

--2009/03/31
--2009/12/31
SET P_CURRENTFISCALYTDDATEEND = P_CURRENTMONTHDATEEND ;
--2007/10/01
--2008/10/01
SET P_LASTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE - 1 YEAR ) -

1900 ) * 10000 + ( P_YEARENDMONTH * 100 ) + P_YEARENDDAY ;

--2008/03/31
SET P_LASTFISCALYTDDATEEND = ( YEAR ( P_CURRENTMONTHDATE - 1 YEAR ) -

1900

) * 10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + ( DAY (
P_CURRENTMONTHDATE - 1 YEAR + 1 MONTH - 1 DAY ) - DAY ( P_CURRENTMONTHDATE

1 YEAR ) ) ;

SET SQLSTMT = '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(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN ? AND ? THEN

AMFLIB.MTHACTE.JQGLVA ELSE 0 END) AS "Current Month Booking Value",

SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN ? AND ? THEN

AMFLIB.MTHACTE.COQTY ELSE 0 END) AS "Current Month Quantity",

SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN ? AND ? THEN

AMFLIB.MTHACTE.JQGLVA ELSE 0 END) AS "Current Fiscal YTD Booking Value",

SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN ? AND ? THEN

AMFLIB.MTHACTE.COQTY ELSE 0 END) AS "Current Fiscal YTD Quantity",

SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN ? AND ? THEN

AMFLIB.MTHACTE.JQGLVA ELSE 0 END) AS "Last Fiscal YTD Booking Value",

SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN ? AND ? THEN

AMFLIB.MTHACTE.COQTY ELSE 0 END) AS "Last Fiscal YTD 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 = ?
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' ;

PREPARE S2 FROM SQLSTMT ;
OPEN C2 USING P_CURRENTMONTHDATESTART , P_CURRENTMONTHDATEEND ,

P_CURRENTMONTHDATESTART , P_CURRENTMONTHDATEEND ,
P_CURRENTFISCALYTDDATESTART , P_CURRENTFISCALYTDDATEEND ,
P_CURRENTFISCALYTDDATESTART , P_CURRENTFISCALYTDDATEEND ,
P_LASTFISCALYTDDATESTART , P_LASTFISCALYTDDATEEND ,

P_LASTFISCALYTDDATESTART

, P_LASTFISCALYTDDATEEND , SELECTION_TERRITORY ;

END

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt

Sent: Tuesday, July 07, 2009 10:12 AM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs

OPEN C2 using startDate, endDate;

Note, the order of the parameters markers determine the order required
for the variables.

HTH,
Charles

On Tue, Jul 7, 2009 at 11:02 AM, ibm<ibm@xxxxxxxxxx> wrote:

Regarding the issue of "incompatible host variable" error with dynamic

SQL, I've been reading that I need to use parameter markers. How do I
replace the markers?

DECLARE SQLSTMT CHAR ( 1024 ) ;
DECLARE C2 CURSOR FOR S2 ;

SET SQLSTMT = 'SELECT
AMFLIB . CUSMAS . COMNO AS "Company Number" ,
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN ? AND ? THEN

AMFLIB.MTHACTE.JQGLVA ELSE 0 END;) AS "Current Month Booking Value

FROM AMFLIB . CUSMAS;';

PREPARE S2 FROM SQLSTMT ;
OPEN C2 ;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt

Sent: Tuesday, July 07, 2009 9:54 AM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs

Ok,

I gotcha...

I think Pat hit the nail on the head, you need a BEGIN and END since
you have more than one statement.

BEGIN
DECLARE MYVAR INTEGER;

DECLARE C1 CURSOR FOR
SELECT AMFLIB.CUSMAS.COMNO
FROM AMFLIB.CUSMAS
WHERE AMFLIB.CUSMAS.CUSNO = 55900;

OPEN C1;
END

HTH,
Charles

On Tue, Jul 7, 2009 at 10:30 AM, ibm<ibm@xxxxxxxxxx> wrote:

I'm using Navigator's SQL Procedure creator to create the procedure.

I don't construct CREATE PROCEDURE. I define my procedure name and
parameters in the GUI.

I did find this:

http://archive.midrange.com/midrange-l/200605/msg00249.html

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt

Sent: Tuesday, July 07, 2009 9:12 AM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs

Again....

we need the entire statement, starting from the CREATE PROCEDURE....

Charles

On Tue, Jul 7, 2009 at 10:02 AM, ibm<ibm@xxxxxxxxxx> wrote:

To keep it simple, this one is throwing the error...

DECLARE MYVAR INTEGER;

DECLARE C1 CURSOR FOR
SELECT
AMFLIB.CUSMAS.COMNO
FROM AMFLIB.CUSMAS
WHERE
AMFLIB.CUSMAS.CUSNO = 55900;

OPEN C1;

"Token MYVAR is not valid. Valid tokens: GLOBAL." SQL0104.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt

Sent: Tuesday, July 07, 2009 8:07 AM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs

Sounds like you may have been missing a semi-colon somewhere when you
were using static.

Please show the entire statement.

Charles

On Tue, Jul 7, 2009 at 8:55 AM, ibm<ibm@xxxxxxxxxx> wrote:

AMFLIB.MTHACTE.TERRN is NUMERIC(2,0).

Parameters:
SELECTION_TERRITORY NUMERIC(2,0) IN
SELECTION_YEAR INTEGER IN
SELECTION_MONTH INTEGER IN

I switched to dynamic SQL because once I started declaring my P_xxxxx

variables I would get a compile error with static SQL.

For example, the below keeps throwing "Token MYVAR is not valid.

Valid

tokens: GLOBAL." SQL0104.

So the only way I was able to figure this out is to switch to dynamic

SQL and the error went away. Is there a way to go back to static?

DECLARE MYVAR INTEGER;

DECLARE C1 CURSOR FOR
SELECT
AMFLIB.CUSMAS.COMNO
FROM AMFLIB.CUSMAS
WHERE
AMFLIB.CUSMAS.CUSNO = 55900;

OPEN C1;

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx

[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt

Sent: Monday, July 06, 2009 3:35 PM
To: Midrange Systems Technical Discussion
Subject: Re: Stored Procedure with different SUMs

What's the data type for AMFLIB . MTHACTE . TERRN

Also, show the parameter declaration section.

Lastly, why are you using dynamic SQL?

Static SQL would perform better and may give you a better error
message during compile...

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(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Month Booking Value",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Month Quantity",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Fiscal YTD Booking Value",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Fiscal YTD Quantity",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Last Fiscal YTD Booking Value",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Last Fiscal YTD 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 = SELECTION_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;

HTH,
Charles

On Mon, Jul 6, 2009 at 4:00 PM, ibm<ibm@xxxxxxxxxx> wrote:

I've been struggling with this all day. All values look OK in the
debugger, but it keeps failing:

SQL State: 22001
Vendor Code: -303
Message: [SQL0303] Host variable *N not compatible

Also, another thing I noticed is if I replace the

SELECTION_TERRITORY

IN

Parm with a hardcoded integer (e.g. 10), *plus* remove the SUM()s,

the

procedure runs.

BEGIN

DECLARE P_CURRENTYEARENDYEAR INTEGER ;
DECLARE P_YEARENDMONTH INTEGER ;
DECLARE P_YEARENDDAY INTEGER ;
DECLARE P_CURRENTMONTHDATE DATE ;
DECLARE P_CURRENTYEARENDDATE DATE ;
DECLARE P_CURRENTMONTHDATESTART DECIMAL ( 7 , 0 ) ;
DECLARE P_CURRENTMONTHDATEEND DECIMAL ( 7 , 0 ) ;
DECLARE P_CURRENTFISCALYTDDATESTART DECIMAL ( 7 , 0 ) ;
DECLARE P_CURRENTFISCALYTDDATEEND DECIMAL ( 7 , 0 ) ;
DECLARE P_LASTFISCALYTDDATESTART DECIMAL ( 7 , 0 ) ;
DECLARE P_LASTFISCALYTDDATEEND DECIMAL ( 7 , 0 ) ;

DECLARE SQLSTMT CHAR ( 1024 ) ;
DECLARE C2 CURSOR FOR S2 ;

SET P_CURRENTMONTHDATE = DATE ( RTRIM ( CHAR ( SELECTION_YEAR ) )

CONCAT

'-' CONCAT RTRIM ( CHAR ( SELECTION_MONTH ) ) CONCAT '-01' ) ;

SET P_YEARENDMONTH = 09 ;
SET P_YEARENDDAY = 30 ;
IF MONTH ( P_CURRENTMONTHDATE ) < P_YEARENDMONTH THEN
SET P_CURRENTYEARENDYEAR = SELECTION_YEAR - 1 ;
ELSE
SET P_CURRENTYEARENDYEAR = SELECTION_YEAR ;
END IF ;
SET P_CURRENTYEARENDDATE = DATE ( RTRIM ( CHAR (

P_CURRENTYEARENDYEAR

)

) CONCAT '-' CONCAT RTRIM ( CHAR ( P_YEARENDMONTH ) ) CONCAT '-'

CONCAT

RTRIM ( CHAR ( P_YEARENDDAY ) ) ) ;
SET P_CURRENTMONTHDATESTART = ( YEAR ( P_CURRENTMONTHDATE ) - 1900 )

*

10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + DAY (
P_CURRENTMONTHDATE ) ;
SET P_CURRENTMONTHDATEEND = ( YEAR ( P_CURRENTMONTHDATE ) - 1900 ) *
10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + ( DAY (
P_CURRENTMONTHDATE + 1 MONTH - 1 DAY ) - DAY ( P_CURRENTMONTHDATE )

)

;

SET P_CURRENTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE ) -

1900

) * 10000 + ( P_YEARENDMONTH * 100 ) + P_YEARENDDAY ;
SET P_CURRENTFISCALYTDDATEEND = P_CURRENTMONTHDATEEND ;
SET P_LASTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE - 1

YEAR

)

- 1900 ) * 10000 + ( P_YEARENDMONTH * 100 ) + P_YEARENDDAY ;
SET P_LASTFISCALYTDDATEEND = ( YEAR ( P_CURRENTMONTHDATE - 1 YEAR )

1900 ) * 10000 + ( MONTH ( P_CURRENTMONTHDATE ) * 100 ) + ( DAY (
P_CURRENTMONTHDATE - 1 YEAR + 1 MONTH - 1 DAY ) - DAY (
P_CURRENTMONTHDATE - 1 YEAR ) ) ;

SET SQLSTMT = '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(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Month Booking Value",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTMONTHDATESTART AND P_CURRENTMONTHDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Month Quantity",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Current Fiscal YTD Booking Value",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_CURRENTFISCALYTDDATESTART AND P_CURRENTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Current Fiscal YTD Quantity",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.JQGLVA ELSE 0) AS "Last Fiscal YTD Booking Value",
SUM(CASE WHEN AMFLIB.MTHACTE.TDATE BETWEEN
P_LASTFISCALYTDDATESTART AND P_LASTFISCALYTDDATEEND THEN
AMFLIB.MTHACTE.COQTY ELSE 0) AS "Last Fiscal YTD 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 = SELECTION_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' ;

PREPARE S2 FROM SQLSTMT ;
OPEN C2 ;

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.

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

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

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

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

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

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