I mainly use this bit of code over and over again to build an IN string for my SQL statements. The first bit takes elements out of an array and builds the IN string like this ...
( 'element1', 'element2', 'element3' ... )
The building of the entire SQL statement can be much more dynamic, but this is more about the IN statement.
( 'element1', 'element2', 'element3' ... )
The building of the entire SQL statement can be much more dynamic, but this is more about the IN statement.
Code:
nbrElements = %lookup( *blanks : acct# ); if nbrElements = 0; nbrAccts = %elem( acct# ); else; nbrAccts = nbrElements - 1; endif; if nbrAccts > 0; accounts = '('; for i = 1 to nbrAccts; if i > 1; accounts += comma; endif; accounts += ' ' + apos + %trim( acct#(i) ) + apos; endfor; accounts += ' )'; endif; sql_stmt = ' select wksol, wkdiv, wksndd, count(*) ' + ' from jplfil/edislsl5 ' + ' where wksol in ' + accounts + ' and wkend=' + weekEndDate + ' group by wksol, wkdiv, wksndd ' + ' order by wksol, wkdiv, wksndd ';