|
On 31-May-2017 11:42 -0600, Glenn Gundermann wrote:
On 31 May 2017 at 13:23, Bill Howie wrote:This may be my issue. The field SMSC# is in the FILEA table, but I
I’m getting an error that I can’t seem to diagnose. I’m using
iSeries Navigator to call a procedure and it’s throwing an
SQL0206 error identifying a field as not being found. I know for
sure the field is in the table I’m accessing so I’m not sure why
the error is occurring.
Here’s my CALL statement:
call library1.program1
(123456,' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ',' ')
And here’s the procedure code:
CREATE OR REPLACE PROCEDURE library1/program1(
IN SRVCTRNUM NUMERIC(6),
IN SRVCTRNAME CHAR(30),
IN SRVCTRADD1 CHAR(30),
IN SRVCTRADD2 CHAR(30),
IN SRVCTRCITY CHAR(20),
IN SRVCTRSTATE CHAR(2),
IN SRVCTRZIP CHAR(9),
IN SRVCTRCTRY CHAR(3),
IN SRVCTRPHONE CHAR(10),
IN SRVCTRFAX CHAR(10),
IN SRVCTREML CHAR(50),
IN SRVCTRSHNM CHAR(15),
IN SRVCTRSTAT CHAR(1),
IN SRVCTRDSP CHAR(1),
INOUT CHANGE_STATUS CHAR(1)
)
LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 0
BEGIN
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE CNTREC NUMERIC(5) DEFAULT 0;
DECLARE SAVESC NUMERIC(5) DEFAULT 0;
SELECT COUNT(*)
INTO CNTREC
FROM FILEA
WHERE SMSC# = SRVCTRNUM;
IF (CNTREC = 0) THEN
SET CHANGE_STATUS = 0;
RETURN;
END IF;
SET SAVESC = SMSC#;
UPDATE FILEA
SET SMSC# = SRVCTRNUM, SMNAME = SRVCTRNAME, SMADD1 = SRVCTRADD1,
SMADD2 = SRVCTRADD2, SMCITY = SRVCTRCITY,SMSTE = SRVCTRSTATE,
SMZIP = SRVCTRZIP, SMCTRY = SRVCTRCTRY, SMTEL# = SRVCTRPHON,
SMFAX# = SRVCTRFAX, SMEMAL = SRVCTREML, SMSHNM = SRVCTRSHNM,
SMSTAT = SRVCTRSTAT, SMMETH = SRVCTRDSP
WHERE SMSC# = SAVESC;
SET CHANGE_STATUS = 1;
RETURN;
END
Any suggestions the group might have on where I’m going wrong
with this would be appreciated. Thanks in advance!
This doesn't make sense to me:
SET SAVESC = SMSC#;
SMSC# isn't defined as a global variable.
Even if it was, SMSC# doesn't have a value at this point in your
program.
was not aware that it had to be defined as a global variable to be
used in the procedure. How is that done?
On 31-May-2017 11:42 -0600, Bill Howie wrote:
the field it can’t find is the first parameter, the numeric one
where I pass it the 123456 value.
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.