Assorted #ibmi hints and tips #4 : Creating an output table directly from a SQL SELECT

Another only recently learned but mega useful SQL trick ...

create table schema/table as (select statement) with [no] data

e.g.
create table MYLIB/MYOUTPUT as (SELECT COLUMN1, COLUMN2, COLUMN3 FROM ...) with data
creates physical file MYOUTPUT in MYLIB with fields COLUMN1, COLUMN2, COLUMN3  and populates it with the SELECT result set, while
create table MYLIB/MYOUTPUT as (SELECT  COLUMN1, COLUMN2, COLUMN3 FROM ...) with no data
creates empty physical file MYOUTPUT in MYLIB with fields COLUMN1, COLUMN2, COLUMN3, and
create table MYLIB/MYOUTPUT as (SELECT  * FROM MYLIB/MYINPUT) with data
does the equivalent of
CPYF MYLIB/MYINPUT MYLIB/MYOUTPUT MBROPT(*ADD) CRTFILE(*YES).


It's worth pointing out that the columns of the SELECT have to have valid field names, thus
create table MYLIB/MYOUTPUT as (SELECT COUNT(*) FROM ...) with data
will not work, while
create table MYLIB/MYOUTPUT as (SELECT COUNT(*) AS MYCOUNT FROM ...) with data
is fine.


The newly announced RUNSQL CL command will make the above even more useful, as it will make it immediately accessible from CL.