Store Procedure Variables
This is a multi-part message in MIME format.
------=_NextPart_000_0012_01C0530F.26DD5C30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
I'm having extreme difficulty trying to figure out why this stored procedure
is complaining when I try to create it. I am getting a syntax error on the
DEFINE lines. I have tried moving BEGIN before and after it and I have
tried other data types as well as different renditions of DECIMAL. Any
suggestions?
Jerame Larsen
CREATE PROCEDURE "root".riviera(cardNum CHAR(25), amt DECIMAL(9,2), id
CHAR(4),
when DATETIME YEAR TO MINUTE, transId INTEGER,
refNum CHAR(30), contract_num INTEGER)
DEFINE prev_bal DECIMAL(9,2);
DEFINE cbal DECIMAL(9,2);
BEGIN
SELECT credit_limit
INTO prev_bal
WHERE contract_id = contract_num;
-- increase for a load
IF id == 'LOAD' THEN
LET cbal = prev_bal + amt;
-- decrease for a remove
ELIF id == 'RMVE' THEN
LET cbal = prev_bal = amt;
-- don't modify the balance if we don't know what it is that's coming
through...
-- ie BBAL records, or if we ever get a CRED or WDRW...these should never
happen
-- with Riviera because their cards are not transactable, but if it does.
ELSE LET cbal = prev_bal;
-- R_Memos Table Update
INSERT (contract_id, type, cbal, extend(date, YEAR TO SECOND), ref_id)
INTO r_memos
VALUES (contract_num, id, cbal, when, refNum);
-- Contract Table Update
UPDATE contract
SET credit_limit = cbal
WHERE contract_id = contact_num;
END PROCEDURE;
------=_NextPart_000_0012_01C0530F.26DD5C30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D635012623-20112000>I'm =
having extreme=20
difficulty trying to figure out why this stored procedure is complaining =
when I=20
try to create it. I am getting a syntax error on the DEFINE =
lines. I=20
have tried moving BEGIN before and after it and I have tried other data =
types as=20
well as different renditions of DECIMAL. Any=20
suggestions?</SPAN></FONT></DIV>
<DIV><FONT face=3DArial size=3D2><SPAN=20
class=3D635012623-20112000></SPAN></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2><SPAN class=3D635012623-20112000>Jerame =
Larsen</SPAN></FONT></DIV>
<DIV><FONT size=3D2><FONT face=3DArial></FONT></FONT> </DIV>
<DIV><FONT size=3D2><FONT face=3DArial>CREATE PROCEDURE =
"root".riviera(cardNum=20
CHAR(25), amt DECIMAL(9,2), id CHAR(4),=20
<BR> when DATETIME YEAR =
TO=20
MINUTE, transId INTEGER,=20
<BR> refNum CHAR(30),=20
contract_num INTEGER)<BR></FONT><BR></FONT><FONT face=3DArial =
size=3D2>DEFINE=20
prev_bal <SPAN =
class=3D635012623-20112000>DECIMAL(9,2)</SPAN>;<BR>DEFINE cbal=20
DECIMAL(9,2);</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT><BR><FONT face=3DArial=20
size=3D2>BEGIN</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>SELECT credit_limit<BR>INTO =
prev_bal<BR>WHERE=20
contract_id =3D contract_num;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>-- increase for a load<BR>IF id =3D=3D =
'LOAD'=20
THEN <BR> LET cbal =3D =
prev_bal +=20
amt;<BR>-- decrease for a remove<BR>ELIF id =3D=3D 'RMVE' THEN =20
<BR> LET cbal =3D prev_bal =3D =
amt;<BR>--=20
don't modify the balance if we don't know what it is that's coming=20
through...<BR>-- ie BBAL records, or if we ever get a CRED or =
WDRW...these=20
should never happen<BR>-- with Riviera because their cards are not =
transactable,=20
but if it does. <BR>ELSE LET cbal =3D prev_bal;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2>-- R_Memos Table Update<BR>INSERT =
(contract_id,=20
type, cbal, extend(date, YEAR TO SECOND), ref_id)<BR>INTO =
r_memos<BR>VALUES=20
(contract_num, id, cbal, when, refNum);</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><BR><FONT face=3DArial size=3D2>-- Contract Table Update<BR>UPDATE=20
contract<BR>SET credit_limit =3D cbal<BR>WHERE contract_id =3D=20
contact_num;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><BR><FONT face=3DArial size=3D2>END PROCEDURE;</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><BR> </DIV></BODY></HTML>
------=_NextPart_000_0012_01C0530F.26DD5C30--
You have a variable called
when
isn't that a reserved word?
Also are you using dbaccess or isql to create the procedure?
You need to use dbaccess, isql does not work!
Well, the BEGIN keyword is not meant to be there. Jerame Larsen wrote in message <8vcd23$oo0$1@news.xmission.com>... >I'm having extreme difficulty trying to figure out why this stored procedure >is complaining when I try to create it. I am getting a syntax error on the >DEFINE lines. I have tried moving BEGIN before and after it and I have >tried other data types as well as different renditions of DECIMAL. Any >suggestions? > >Jerame Larsen
WHEN is a reserved keyword, I think. Try changing the name of the parameter. HTH Bogdan Jerame Larsen wrote: > This is a multi-part message in MIME format. > > ------=_NextPart_000_0012_01C0530F.26DD5C30 > Content-Type: text/plain; > charset="iso-8859-1" > Content-Transfer-Encoding: 7bit > > I'm having extreme difficulty trying to figure out why this stored procedure > is complaining when I try to create it. I am getting a syntax error on the > DEFINE lines. I have tried moving BEGIN before and after it and I have > tried other data types as well as different renditions of DECIMAL. Any > suggestions? > > Jerame Larsen > > CREATE PROCEDURE "root".riviera(cardNum CHAR(25), amt DECIMAL(9,2), id > CHAR(4), > when DATETIME YEAR TO MINUTE, transId INTEGER, > refNum CHAR(30), contract_num INTEGER) >