MIDRANGE dot COM Mailing List Archive



Home » MIDRANGE-L » July 2009

Re: Stored Procedure with different SUMs - STATIC SQL



fixed

select * from table
where ( SELECTION_TERRITORY <= 0 and territory = 1 )
or ( SELECTION_TERRITORY > 0 and territory in (select terrn in
terrtable));


Note: use of SELECT * in production code is not recommended.

Charles

On Fri, Jul 31, 2009 at 11:52 AM, ibm<ibm@xxxxxxxxxx> wrote:
Select * from table where territory = 1;

The other:

Select * from table where territory in (select terrn in terrtable);

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, July 31, 2009 9:47 AM
To: Midrange Systems Technical Discussion
Subject: RE: Stored Procedure with different SUMs - STATIC SQL

May we see a bit of the SELECT statement to determine why the need for
two
cursors?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
"ibm" <ibm@xxxxxxxxxx>
To:
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date:
07/31/2009 10:39 AM
Subject:
RE: Stored Procedure with different SUMs - STATIC SQL
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



Ideally I would like to do:

IF SELECTION_TERRITORY > 0 THEN
                DECLARE C1 CURSOR FOR
                                SELECT ... some stmt
                OPEN C1 ;
ELSE
                DECLARE C2 CURSOR FOR
                                SELECT .... some stmt
                OPEN C2 ;
END IF ;

But the IF statement can not be called before DECLARing a cursor.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, July 31, 2009 8:35 AM
To: Midrange Systems Technical Discussion
Subject: RE: Stored Procedure with different SUMs - STATIC SQL

I was thinking that maybe a CASE clause may work but case is often based

on the contents of column - not a selection or host variable.

For example if you have a table that looks like this
item
sales2009
sales2008
sales2007
...
you want to do something like
case :hostvar
 when '2009' then sum(sales2009)
 when '2008' then sum(sales2008)
end;

I am not sure this can be done.  Has your DBA heard of something called
"Third Normal Form"?

Rob Berendt
--
Group Dekko Services, LLC
Dept 01.073
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From:
"ibm" <ibm@xxxxxxxxxx>
To:
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date:
07/31/2009 09:08 AM
Subject:
RE: Stored Procedure with different SUMs - STATIC SQL
Sent by:
midrange-l-bounces@xxxxxxxxxxxx



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.







Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2014 by MIDRANGE dot 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 here. If you have questions about this, please contact