Tweet
Another twist on the last trick Joe mentioned...You can create the QTEMP table and populate it at the same with one SQL statement. Try the DECLARE GLOBAL TEMPORY TABLE statement.
DELCARE GLOBAL TEMPORARY TABLE ORDHDR AS (SELECT * FROM PRODFILES/ORDHDR WHERE OHSTS = 'C' and HLACT < (TODAY()-30 days)))
WITH DATA WITH REPLACE;
The statement above creates a copy of ORDHDR in QTEMP library of the current job and populates it with the SELECT statement. WITH DATA says populate it. WITH NO DATA doesn't populate the table. (In that case you might as well use the LIKE table-expression instead of the AS which is also supported.) WITH REPLACE says replace the ORDHDR in QTEMP if there already is one there.
This statement comes in real handy for creating files, joins, whatever you need as a "driving" file of data (temporarily) from which you read and print, read and display...you get it. It creates a table from whatever is in your select statement. You can use this SQL statement to cull all of your data gathering and summarizing I/O to one SQL statement to drive your report, inquiry, whatever. I use it a lot. Very powerful.
DELCARE GLOBAL TEMPORARY TABLE ORDHDR AS (SELECT * FROM PRODFILES/ORDHDR WHERE OHSTS = 'C' and HLACT < (TODAY()-30 days)))
WITH DATA WITH REPLACE;
The statement above creates a copy of ORDHDR in QTEMP library of the current job and populates it with the SELECT statement. WITH DATA says populate it. WITH NO DATA doesn't populate the table. (In that case you might as well use the LIKE table-expression instead of the AS which is also supported.) WITH REPLACE says replace the ORDHDR in QTEMP if there already is one there.
This statement comes in real handy for creating files, joins, whatever you need as a "driving" file of data (temporarily) from which you read and print, read and display...you get it. It creates a table from whatever is in your select statement. You can use this SQL statement to cull all of your data gathering and summarizing I/O to one SQL statement to drive your report, inquiry, whatever. I use it a lot. Very powerful.