Re: Stored procedure error

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>>