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

Correct.

Basically, a significant amount (most?) of the work done by the
CQE/SQE is done during the PREPARE step. With a static statement, the
PREPARE step is implicitly done during the compile and the resulting
meta data gets stored inside the *PGM object.

So rules to live by
1) Use static statement wherever possible
2) Don't re-prepare a statement that hasn't changed
3) Use parameter markers in dynamic statements

Regarding 2 and 3...consider the following:

exec SQL declare C1 cursor for S1;
wUserFile = DetermineUserFile();
//more code
dow not wDone;
//more code
if wCustNbr <> wLastCustNbr;
exec SQL close C1;
wSQLstatement = 'select * from ' + wUserFile
+ ' where custnbr = ' + %char(wCustnbr);

exec SQL prepare S1 from :wSQLstatement;

exec SQL open C1;
endif;
exec SQL fetch next from C1 into :myDS;
//more code
enddo;

I have to use dynamic SQL becuase the file name is determined by the
user running the program.
However, there's no need to prepare the statement more than once.
Instead, by user parameter markers properly, I only need to prepare it
once.

exec SQL declare C1 cursor for S1;
wUserFile = DetermineUserFile();
wSQLstatement = 'select * from ' + wUserFile
+ ' where custnbr = ?';
exec SQL prepare S1 from :wSQLstatement;
//more code
dow not wDone;
//more code
if wCustNbr <> wLastCustNbr;
exec SQL close C1;
exec SQL open C1 using :wCustNbr;
endif;
exec SQL fetch next from C1 into :myDS;
//more code
enddo;

As a bonus, the use of parameter markers protect you from SQL injection attacks.

HTH,
Charles

On Fri, Jul 31, 2009 at 3:54 PM, Neill Harper<neill.harper@xxxxxxxx> wrote:

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

is

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

on

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.

So

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.

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