RE: Embedded SQL in RPGLE pgm

Select statements, imbedded, require a cursor if they return more than one 
row.  If they do not return more than one row, then change your "select *" 
to "select * into..."
After the prepare use Execute.

Another option on a prepared statement is to use parameter markers.
 /free
  mainStatement='Select * into :CapmstrDS ' +
                'from capmstr ' +
                'where (CRECA = ''A'') AND ' +
                       'CPROP1 like ? and ' +
                       'CSTRET = ? and '+
                       'CAPT = 0) ' +
                'order by CPROP1';
 /end-free
C/exec sql
C+ prepare stmt1 from :mainStatement
C/end-sql
 /free
  x=%Trim(inCivic) + '%';
  y=%Char(inStreet);
 /end-free
C/exec sql
C+ execute stmt1 using :x, :y
C/end-sql

Again, logic will vary if using a cursor.  Then it will look more like:
 /free
  mainStatement='Select * ' +
                'from capmstr ' +
                'where (CRECA = ''A'') AND ' +
                       'CPROP1 like ? and ' +
                       'CSTRET = ? and '+
                       'CAPT = 0) ' +
                'order by CPROP1';
 /end-free
C/exec sql
C+ prepare stmt1 from :mainStatement
C/end-sql
C/exec sql
C+ declare Cur1 cursor for stmt1
C/end-sql
 /free
  x=%Trim(inCivic) + '%';
  y=%Char(inStreet);
 /end-free
C/exec sql
C+ Open Cur1 using :x, :y
C/end-sql
 // fetch loop
C/exec sql
C+ fetch Cur1 into :capmstrDS
C/end-sql




Rob Berendt