RPGLE SQL0511 for Update error.

Just when I think I have a handle on RPGLE Embedded SQL, SQL reaches out and slaps me silly.

I created a daily sales retrieval program using RPGLE Free and Embedded SQL which writes XML data to the /QNTC file system (windows share). I wanted to update the status and date field  after I write out the XML. I thought a simple to do, I have already done something similar with no problem. Then.....

SQL0511

SQL0511N

The FOR UPDATE clause is not allowed because the table specified by the cursor cannot be modified.
Explanation:
The result table of the SELECT or VALUES statement cannot be updated.
On the database manager, the result table is read-only if the cursor is based on a VALUES statement or the SELECT statement contains any of the following:

• The DISTINCT keyword
• A column function in the SELECT list
• A GROUP BY or HAVING clause
• A FROM clause that identifies one of the following:
• More than one table or view
• A read-only view
• An OUTER clause with a typed table or typed view
• A set operator (other than UNION ALL).
• A FROM clause that identifies one of the following:
• More than one table or view
• A read-only view
• An OUTER clause with a typed table or typed view
• A data change statement

Note that these conditions do not apply to subqueries of the SELECT statement.
The statement cannot be processed.
User response:
Do not perform updates on the result table as specified.
Federated system users: isolate the problem to the data source failing the request (see the problem determination guide for procedures to follow to identify the failing data source). If a data source is failing the request, examine the restrictions for that data source to determine the cause of the problem and its solution. If the restriction exists on a data source, see the SQL reference manual for that data source to determine why the object is not updatable.
sqlcode: -511
sqlstate: 42829

Oh me oh my. I don’t have any more time to spend on this and decided to skip the update of the status and data field at this time. The program is called remotely from a batch file that will also run a step that processes the XML into the Midretail allocation program. I will capture the result of the update to Midretail in the batch program and if successful will send another remote command to update the status and date of the records processed.

Exec SQL declare mainCursor cursor
         for Select stat, vstr, vdte, sum(vqty), vcls, vven, +
             vsty, vclr, vstp, vdtp
             from MidSalesPF
             where vdte = :yesterday
             group by stat, vstr, vdte, vcls, vven, vsty, vclr, vstp, vdtp
             order by vstr, vdte, vdte, vcls, vven, vsty, vclr, vstp;

      Exec SQL open mainCursor;                 // SQL open cursor

      Exec SQL fetch next from mainCursor       // Load data Structure
            into :mainds;

      exsr ClrHdr;                    // Clear or create XML header record

      dow sqlstt = '00000' ;           // Continue if no SQL error [DO Loop]
       exsr  Load_SSColSR;             // Write detail XML records to IFS

       Exec SQL fetch next from mainCursor      // Get next record
             into :mainds;
      enddo ;                                                      
      Exec SQL close mainCursor;  // Close SQL file   

~Richard

The road to success is dotted with many tempting parking places.  ~Author Unknown