RE: DDL question

Re: " But it's a bit more complicated if you also need columns the you want to define yourself - you can try it with fixed values and CAST, but that doesn't work always perfect..."

Just yesterday I created this table:
Create table PTISYS/ALLJOBDS (
SEQ INTEGER ,
SERVER VARCHAR(18) CCSID 37 ,
ERRORMSG VARCHAR(512) CCSID 37 ,
LIKE qsys2.job_description_info INCLUDING COLUMN DEFAULTS
)
RCDFMT rALLJOBDS ;

Admittedly, this doesn't select only certain columns from qsys2.job_description_info, so I'm not sure the syntax checker would balk. I suppose that if it didn't work, you could always create the table with the SELECT AS only, then follow it with an ALTER TABLE to add your own columns.

- Dan Bale

-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Tuesday, September 23, 2025 11:48 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: DDL question

Hi Doug,

you can create a table from a SELECT statement - that's not the problem:
CREATE TABLE ... AS (
SELECT ... AS ...,
... AS ...,
FROM ...
)
DEFINITION ONLY
INCLUDING COLUMN DEFAULTS
RCDFMT ...;
This creates the table with the column definitions from the SELECT.

Of course you can also use multiple "reference" tables, but using JOIN.

But it's a bit more complicated if you also need columns the you want to define yourself - you can try it with fixed values and CAST, but that doesn't work always perfect:
CREATE TABLE ... AS (
SELECT CAST('' AS CHAR(50)) AS ...,
... AS ...,
FROM ...
)
DEFINITION ONLY
INCLUDING COLUMN DEFAULTS
RCDFMT ...;
Or you create the table with SELECT first, and then add the additional columns with ALTER TABLE ADD COLUMN.

HTH and kind regards,
Daniel

*** CONFIDENTIALITY NOTICE: The information contained in this communication may be confidential, and is intended only for the use of the recipients named above. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution, or copying of this communication, or any of its contents, is strictly prohibited. If you have received this communication in error, please return it to the sender immediately and delete the original message and any copy of it from your computer system. If you have any questions concerning this message, please contact the sender. ***