RPGLE embedded SQL 'select from final table...' throwing SQL0029 at compile time

Question

What looks like a simple SQL statement is throwing error SQL0029... 'Position 14 INTO clause missing from embedded statement.'.
The first obvious point is that the 'INTO' is not missing from my statement but the SQL pre-compiler sometimes provides messages that don't directly relate to the real cause.

I have broken the statement down to its simplest steps and built it up in stages. All steps I've taken have compiled in the SQL pre-compiler. Only when I insert 'select UFVAID from final table...' does it throw the error.

The program is defined as SQLRPGLE and it contains other sql statements that are not objected to.

The value I want to retrieve from the insert is an auto-increment field that is to be used in subsequent inserts.

Any hints would be great. Thanks.

Statement

Exec Sql                                                     
  select UFVAID from final table (                           
  insert into elglvat                                        
    (UFVASE, UFTNAL, UFTNNU, UFVINT, UFVDEC, UFVALS,         
     UFVALM, UFVDAT, UFVTIM, UFVTSP)                         
  values                                                     
    (:UFVASE, :UFTNAL, :UFTNNU, :UFVINT, :UFVDEC, :UFVALS,   
     :UFVALM, :UFVDAT, :UFVTIM, :UFVTSP)                     
     ) ;

score 3 · Accepted Answer · answered Mar 08 '20 at 05:02

You are missing the into statement on the select clause that puts the result into the RPG variable. This is not to be confused with the insert clause into.

Exec Sql                                                     
  select UFVAID 
    into :MyRpgVar // <===== Missing this line
  from final table (                           
  insert into elglvat                                        
    (UFVASE, UFTNAL, UFTNNU, UFVINT, UFVDEC, UFVALS,         
     UFVALM, UFVDAT, UFVTIM, UFVTSP)                         
  values                                                     
    (:UFVASE, :UFTNAL, :UFTNNU, :UFVINT, :UFVDEC, :UFVALS,   
     :UFVALM, :UFVDAT, :UFVTIM, :UFVTSP)                     
     ) ;

score 0 · Answer 2 · answered Apr 01 '20 at 19:12

Player1st gives you a good way to retrieve the value from a data change table reference, but if you are only inserting a single row, you can retrieve the most recently generated identity using IDENTITY_VAL_LOCAL().

So

Exec Sql                                                     
  insert into elglvat                                        
    (UFVASE, UFTNAL, UFTNNU, UFVINT, UFVDEC, UFVALS,         
     UFVALM, UFVDAT, UFVTIM, UFVTSP)                         
  values                                                     
    (:UFVASE, :UFTNAL, :UFTNNU, :UFVINT, :UFVDEC, :UFVALS,   
     :UFVALM, :UFVDAT, :UFVTIM, :UFVTSP);

Then

Exec sql
  insert into <a detail table>
    values(IDENTITY_VAL_LOCAL, FIELD1, FIELD2, ...);

RPGLE embedded SQL 'select from final table...' throwing SQL0029 at compile time

2 Answers2