RE: Store Procedure Variables
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C053C5.9F0C73B0
Content-Type: text/plain;
charset="iso-8859-1"
You know what your missing the FROM clause in your
SELECT statement.
SELECT credit_limit
FROM <whats_the_table_name>
INTO prev_bal
WHERE contract_id = contract_num;
You should have a corresponding END whenever you use
BEGIN. And why are you using "==" you should just us "=" to test
for a condition. Lastly, you should have an END IF to close
an IF statement.
Please read Informix SQL Guide: Syntax for more information.
Don
-----Original Message-----
From: Jerame Larsen [mailto:jerame@powerconnect.com]
Sent: Monday, November 20, 2000 6:30 PM
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_001_01C053C5.9F0C73B0
Content-Type: text/html;
charset="iso-8859-1"
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<META content="MSHTML 5.00.2314.1000" name=GENERATOR></HEAD>
<BODY>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=664291114-21112000>You
know what your missing the FROM clause in your</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=664291114-21112000>SELECT
statement.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=664291114-21112000></SPAN></FONT> </DIV>
<DIV><SPAN class=664291114-21112000>
<DIV><FONT color=#0000ff face=Arial size=2>SELECT credit_limit</FONT></DIV>
<DIV><SPAN class=664291114-21112000></SPAN><FONT color=#0000ff face=Arial
size=2>F<SPAN class=664291114-21112000>ROM
<whats_the_table_name></SPAN><BR>INTO prev_bal<BR>WHERE contract_id =
contract_num;</FONT></DIV>
<DIV> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=664291114-21112000>You
should have a corresponding END whenever you use</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=664291114-21112000>BEGIN.
</SPAN></FONT><FONT color=#0000ff face=Arial size=2><SPAN
class=664291114-21112000>And why are you using "==" you should just us "=" to
test</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=664291114-21112000>for a
condition. Lastly, you should have an END IF to close</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=664291114-21112000>an IF
statement.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=664291114-21112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN class=664291114-21112000>Please
read Informix SQL Guide: Syntax for more information.</SPAN></FONT></DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=664291114-21112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=664291114-21112000></SPAN></FONT> </DIV>
<DIV><FONT color=#0000ff face=Arial size=2><SPAN
class=664291114-21112000>Don</SPAN></FONT></DIV><FONT size=2>
<DIV><FONT color=#0000ff><FONT face=Arial> <BR></FONT></FONT><FONT
color=#0000ff><FONT face=Arial><SPAN
class=664291114-21112000> </SPAN></FONT></FONT></SPAN></FONT><FONT
face=Tahoma><FONT size=2>-----Original Message-----<BR><B>From:</B> Jerame
Larsen [mailto:jerame@powerconnect.com]<BR><B>Sent:</B> Monday, November 20,
2000 6:30 PM<BR><B>To:</B> Informix List<BR><B>Subject:</B> Store Procedure
Variables<BR><BR></DIV></FONT></DIV>
<BLOCKQUOTE style="MARGIN-RIGHT: 0px"></FONT>
<DIV><FONT face=Arial size=2><SPAN class=635012623-20112000>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?</SPAN></FONT></DIV>
<DIV><FONT face=Arial size=2><SPAN
class=635012623-20112000></SPAN></FONT> </DIV>
<DIV><FONT face=Arial size=2><SPAN class=635012623-20112000>Jerame
Larsen</SPAN></FONT></DIV>
<DIV><FONT size=2><FONT face=Arial></FONT></FONT> </DIV>
<DIV><FONT size=2><FONT face=Arial>CREATE PROCEDURE "root".riviera(cardNum
CHAR(25), amt DECIMAL(9,2), id CHAR(4),
<BR> when DATETIME YEAR TO
MINUTE, transId INTEGER,
<BR> refNum CHAR(30),
contract_num INTEGER)<BR></FONT><BR></FONT><FONT face=Arial size=2>DEFINE
prev_bal <SPAN class=635012623-20112000>DECIMAL(9,2)</SPAN>;<BR>DEFINE
cbal DECIMAL(9,2);</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT><BR><FONT face=Arial
size=2>BEGIN</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>SELECT credit_limit<BR>INTO prev_bal<BR>WHERE
contract_id = contract_num;</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>-- increase for a load<BR>IF id == 'LOAD'
THEN <BR> LET cbal = prev_bal
+ amt;<BR>-- decrease for a remove<BR>ELIF id == 'RMVE' THEN
<BR> LET cbal = prev_bal =
amt;<BR>-- don't modify the balance if we don't know what it is that's coming
through...<BR>-- ie BBAL records, or if we ever get a CRED or WDRW...these
should never happen<BR>-- with Riviera because their cards are not
t