RE: Store Procedure Variables
This is a multi-part message in MIME format.
------=_NextPart_000_0006_01C0539F.9F06C3A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Are you using dbaccess or isql to create the procedure, the latter does not
accept SPL syntax.
Chris
-----Original Message-----
From: owner-informix-list@iiug.iiug.org
[mailto:owner-informix-list@iiug.iiug.org]On Behalf Of Jerame Larsen
Sent: Monday, November 20, 2000 11:30
To: Informix List
Subject: Store Procedure Variables
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_0006_01C0539F.9F06C3A0
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 content=3D"text/html; charset=3Diso-8859-1" =
http-equiv=3DContent-Type>
<META content=3D"MSHTML 5.00.2614.3500" name=3DGENERATOR></HEAD>
<BODY>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN =
class=3D590384209-21112000>Are=20
you using dbaccess or isql to create the procedure, the latter does not =
accept=20
SPL syntax.</SPAN></FONT></DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D590384209-21112000></SPAN></FONT> </DIV>
<DIV><FONT color=3D#0000ff face=3DArial size=3D2><SPAN=20
class=3D590384209-21112000>Chris</SPAN></FONT></DIV>
<BLOCKQUOTE style=3D"MARGIN-RIGHT: 0px">
<DIV align=3Dleft class=3DOutlookMessageHeader dir=3Dltr><FONT =
face=3DTahoma=20
size=3D2>-----Original Message-----<BR><B>From:</B>=20
owner-informix-list@iiug.iiug.org=20
[mailto:owner-informix-list@iiug.iiug.org]<B>On Behalf Of </B>Jerame=20
Larsen<BR><B>Sent:</B> Monday, November 20, 2000 11:30<BR><B>To:</B> =
Informix=20
List<BR><B>Subject:</B> Store Procedure Variables<BR><BR></DIV></FONT>
<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=20
I try to create it. I am getting a syntax error on the DEFINE=20
lines. I have tried moving BEGIN before and after it and I have =
tried=20
other data types as well as different renditions of DECIMAL. Any =
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=20
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=20
cbal 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 =
<BR> LET cbal =3D prev_bal =
=3D=20
amt;<BR>-- 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=20
transactable, 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></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_0006_01C0539F.9F06C3A0--
Related threads
- Conversion to differeent characters sets
- Control of CLIENT_LOCALE and DB_LOCALE at connection (doc issue?)
- Problem in changing locale via dbexport/dbimport
- Re: Matches not Work in 7.30
- DATABASE vs CONNECT TO: Invalid characters error?