Re: SQL0206 error

[Glenn Gundermann <glenn.gundermann@xxxxxxxxx>]

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