Re: Dynamic SQL

On 08-Dec-2014 11:44 -0600, CRPence wrote:

On 08-Dec-2014 10:26 -0600, Billy Waters wrote:

My first attempt at using dynamic SQL. Any help would be greatly
appreciated.

w2 = 'R1';
w3 = 'R9';
w4 = 2015;
w5 = 4;

WSQL = 'select sum(pinv) into ? from CMSFWRK20 ' +
'where PCLS between ? and ? ' +
'and PYEAR = ? and PPERD = ?';

Use VALUES ... INTO

Exec Sql prepare S1 from :WSQL;

Check for errors; the "Prepared statement S1 not found." is the
expected error to occur when the prior PREPARE failed. Operating
with return-codes, after the invocations, the return-codes should be
tested to verify the success or failure of the preceding request.

Exec SQL
execute S1 using :w1, :w2, :w3, :w4, :w5;

job log shows:
CALL TEST
Token ? was not valid. Valid tokens: :<IDENTIFIER>.

That is the error that caused the PREPARE to fail. Had the code
detected that failure, then the EXECUTE could have been logically
bypassed; perhaps the program would send its own message to diagnose
the failure [of the PREPARE].

Prepared statement S1 not found.

Try the following [untested] VALUES INTO in place of what was
attempted originally with SELECT INTO:

WSQL =
'values(' +
' select sum(PINV)' +
' from CMSFWRK20 ' +
' where PCLS between ? and ?' +
' and PYEAR = ?' +
' and PPERD = ?' +
' ) into ?' ;

Exec SQL
prepare ValIntoRqs from :WSQL ;

// check for errors; respond accordingly

Exec SQL
execute ValIntoRqs using :w1, :w2, :w3, :w4, :w5 ;

// check for errors; respond accordingly

As implied by Michael's reply, the order of the host variables in the USING clause would have to be rearranged if not instead recoded to match the new order within the VALUES INTO statement versus the originally coded SELECT INTO statement; as noted, what I offered was not tested. So, for example, the following modification to the above EXECUTE:

Exec SQL
execute ValIntoRqs using :w2, :w3, :w4, :w5, :w1 ;