Re: Stored procedure error

[CRPence <CRPbottle@xxxxxxxxx>]

  I infer the "returns no data" was determined to have its origin 
in the noted sql code -206; i.e. per the msgSQL0206, no data is 
returned.?  Does the message, when spooled, show "Column WLQMTO not" 
or some subtle variation such as "Column WLQMTO  not"?  What is the 
other message replacement text [in second level text of] the 
SQL0206, beyond just &1 for the column name [shown in the first 
level text]?  FWiW the SQL0205 message text diagnoses a similar 
condition.

  Does the PRTSQLINF of the RPG program show the same as expected 
statement; i.e. consistent with the statement apparently copied from 
the source?  Be sure to first use OVRPRTF RPLUNPRT(*YES '%') [or 
another replacement character], to ensure that any unprintable 
characters would appear visually.

  By stating "it does" exist, the field named WLQMTO, does DSPFFD 
of the file F3112 show that field and DSPFFD of the file F4801 does 
not?  Since they are not qualified table names, the SQL0206 names 
the library to use on the DSPFFD; with *system naming, the file 
should be from the *LIBL established in the job when the statement 
ran, which may not be known without DSPJOB taken at the point of 
failure.

Regards, Chuck

sjl wrote:
> A coworker is having problems with a stored procedure which uses 
> an RPGIV program.
>
> In the job log for the job which is servicing the requests from 
> the VB.Net program, the following error messages are appearing
> (my notes in parentheses):
>
> User XXXXXXXXX from client xxx.xx.xxx.xxx connected to server.
> Library xxxxxxxxxx added to library list. (data library)
> Library yyyyyyyyy added to library list. (object library)
> Column WLQMTO not in specified tables.  (it is!)
> Cursor SOQ not open.
>
> See his email message below...
>
> > <<SNIP>>
> >
> > Something has happened between September and now that has
> > caused this issue.
> >
> > I created the stored procedure using the following command:
> >
> >         Create Procedure QGPL/Proc_AutoMill_Lens_Data
> >           (In Parm1 Char(8))
> >           Result Set 1
> >           Language RPGLE
> >           Not Deterministic
> >           Reads SQL Data
> >           External Name ATM001R
> >           Parameter Style General
> >
> > The actual SQL that is executed is embedded in an RPG program
> > (ATM001R) and  the following is the statement executed:
> >
> >         Select Substr(WALITM,1,6) As Style,
> >                ConCat(Trim(WAWR03),
> >                  Substr(WALITM,8,3))
> >                  As Power,
> >                WAUORG As Started,
> >                WAUORG - WLQMTO As Rejects
> >           From F4801
> >                  Join
> >                F3112
> >                  On WADOCO = WLDOCO
> >         Where WALOTN = :LotNumber
> >            And WLQMTO <> 0
> >
> > <<SNIP>>