× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Bill,

I'm not sure if this is the cause.
You've defined CHANGE_STATUS as CHAR and yet you are setting the value to a
numeric.


Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (905) 486-1162 x 239
Cell: (416) 317-3144


On 31 May 2017 at 13:23, Bill Howie <William.Howie@xxxxxxxxxxxxxxxx> wrote:

Hello all,

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!

Bill Howie
william.howie@xxxxxxxxxxxxxxxx


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.