Re: SQL0206 error

Hi Bill,

It appears you are missing the 'e' on phone:

This statement:
SMTEL# = SRVCTRPHON

should be:
SMTEL# = SRVCTRPHONE



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