|
So are you saying that SETting a variable after DECLARING a cursor is
still ok? The values of the variables will not be used until the Cursor
is opened?
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, July 07, 2009 9:58 AM
To: Midrange Systems Technical Discussion
Subject: RE: Stored Procedure with different SUMs
I got an error very similar to yours. Some of my sample code was:
Create Procedure
rob.deletethisprocedure ( In customerNumber dec (6,0))
Language SQL
Begin
Declare MyVar int;
Declare MyResult int;
Declare WorkLines int;
Set MyVar = 3;
Declare C1 cursor for
select hlins
from gdidivf.ech
where hcust=customerNumber;
...
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token C1 was not valid. Valid tokens: GLOBAL. Cause .
.
. . . : A syntax error was detected at token C1. Token C1 is not a
valid token. A partial list of valid tokens is GLOBAL. This list
assumes
that the statement is correct up to the token. The error may be earlier
in the statement, but the syntax of the statement appears to be valid up
to this point. Recovery . . . : Do one or more of the following and
try
the request again: -- Verify the SQL statement in the area of the token
C1. Correct the statement. The error could be a missing comma or
quotation mark, it could be a misspelled word, or it could be related to
the order of clauses. -- If the error token is <END-OF-STATEMENT>,
correct
the SQL statement because it does not end with a valid clause.
All I did to fix it was change the order to
Create Procedure
rob.deletethisprocedure ( In customerNumber dec (6,0))
Language SQL
Begin
Declare MyVar int;
Declare MyResult int;
Declare WorkLines int;
Declare C1 cursor for
select hlins
from gdidivf.ech
where hcust=customerNumber;
Set MyVar = 3;
...
The error was that I had a Declare after an executable. Next time humor
the list and post the entire code. Now you see why?
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/07/2009 10:31 AM
Subject:
RE: Stored Procedure with different SUMs
Sent by:
midrange-l-bounces@xxxxxxxxxxxx
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...mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
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 [
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.
Valid
For example, the below keeps throwing "Token MYVAR is not 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?
mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Charles Wilt
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 [
.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
SELECTION_TERRITORYB0GLCD
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
IN
theParm with a hardcoded integer (e.g. 10), *plus* remove the SUM()s,
CONCATprocedure 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 ) )
P_CURRENTYEARENDYEAR'-' 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 (
)
CONCAT) CONCAT '-' CONCAT RTRIM ( CHAR ( P_YEARENDMONTH ) ) 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 )
;
1900SET P_CURRENTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE ) -
YEAR) * 10000 + ( P_YEARENDMONTH * 100 ) + P_YEARENDDAY ;
SET P_CURRENTFISCALYTDDATEEND = P_CURRENTMONTHDATEEND ;
SET P_LASTFISCALYTDDATESTART = ( YEAR ( P_CURRENTYEARENDDATE - 1
)
-- 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
CUSMASCUSCL AS "Customer Class" ,
AMFLIB . CUSMAS . CUSNO AS "Customer Number" , AMFLIB .
.
SLSMASCUSNM AS "Customer Name" ,
AMFLIB . CUSMAS . SLSNO AS "Salesman Number" , AMFLIB .
.
.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
MBB0REPSLSNO
JOIN AMFLIB . MBB0REP
ON AMFLIB . MTHACTE . ITCLS = AMFLIB .
.
mailingB0GLCD
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)
list
mailingTo 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)
list
mailingTo 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)
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 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.
midrange.com
