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 ;


This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].