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


  1.  Home
  2. MIDRANGE-L
  3. July 2009

AW: Stored Procedure with different SUMs

Hi,

you need a specific sequence within your SQL source code!
1. Define all your all variables
2. Define all your conditions
3. Define all your return values
4. Define all your cursors
5. Define all your condition, exception and continue handler

... begin with your source code.

That means you need to move the SET-Statemet after the declare section.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von ibm
Gesendet: Tuesday, 07. July 2009 16:59
An: Midrange Systems Technical Discussion
Betreff: RE: Stored Procedure with different SUMs


Why does this not work then?

Begin
DECLARE myvar INTEGER;

SET myvar = 12;

DECLARE cursor1 CURSOR FOR SELECT * FROM table1;
End


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



As an Amazon Associate we earn from qualifying purchases.

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