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

Answer 1

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