Re: SQL0206 error

On 31-May-2017 12:04 -0600, Bill Howie wrote:

On 31-May-2017 11:42 -0600, Glenn Gundermann wrote:

On 31 May 2017 at 13:23, Bill Howie wrote:

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.

This may be my issue. The field SMSC# is in the FILEA table, but I
was not aware that it had to be defined as a global variable to be
used in the procedure. How is that done?

I do not expect a global variable is what the given procedure needs or wants, to effect a "correction". I expect the SAVESC variable is superfluous; and of note, also that variable is not fully compatible with SRVCTRNUM. If SAVESC is to be used, instead of SRVCTRNUM in the UPDATE statement, then what is presumably the problematic statement, i.e. the "SET SAVESC = SMSC#;", should instead be written as "SET SAVESC = SRVCTRNUM;"

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.

In both expectation and empirically, the error is with the reference to SMSC# in the SET statement; i.e. msg SQL0206 "Column or global variable SMSC# not found." If SMSC# is NUMERIC(6), then the SAVESC variable and all lines with reference to that variable could just as well be deleted; then just refer to SRVCTRNUM in the WHERE clause of the UPDATE, or refer instead to CAST(SRVCTRNUM as NUMERIC(5)) if SMSC# is NUMERIC(5). Regardless, the first column in the SET clause of the UPDATE is apparently redundant; i.e. the value will be set to what the value already is.

The next problem [after a /correction/ as alluded above] would be yet another SQL0206, but this time for the parameter-variable named SRVCTRPHONE but for which the reference in the UPDATE is incorrectly coded as SRVCTRPHON [as Glenn noted in another reply]