Here’s a CL Command I didn’t know about, RUNSQL, which allows an SQL statement to be run from within a CL program without needing a source file.
Inevitably, I found this because I was looking for a quick solution to a transitory problem. I won’t bore you with the details but, what I wanted to do was create a temporary file, populate it with orders for a (user specified) date and then copy this file into a library that has been made available to an external ftp application.
Here is what I ended up with (field, file and library names have been changed for clarity and to protect the innocent):
pgm &date dcl &date *char 8 dcl &statement *char 150 dcl &nbrrcd *dec (10 0) cpyf fromfile(template/orders) tofile(qtemp/orders) + mbropt(*add) crtfile(*yes) clrpfm qtemp/orders chgvar &statement value('insert into qtemp/orders + select * from archlib/orders + where msgdat = ' *cat &date) runsql sql(&statement) commit(*none) rtvmbrd qtemp/orders nbrcurrcd(&nbrrcd) if cond(&nbrrcd *gt 0) then(do) cpyf fromfile(qtemp/orders) tofile(ftplib/orders) + mbropt(*add) crtfile(*yes) enddo dltf qtemp/orders endpgm
It’s all pretty simple stuff but being able to embed the SQL statement right in the CL makes a conceptually simple solution very simple to implement.