Re: SQL Create Table As Select with Constraints

On 25-May-2016 09:49 -0500, Bruce Guetzkow wrote:

Is it possible to create an SQL Table using "as select" and include
constraints in the same statement? Here is an example of what I have
tried to do:

CREATE TABLE mylib.BGTABLEA
( mypro7
, mylocnam
, CONSTRAINT BGTABLEA_PRIMARYKEY
PRIMARY KEY ( mypro7 )
, CONSTRAINT BGTABLEA_CHKMYLOCNAM CHECK ( mylocnam<> ' ' )
)
AS ( SELECT a.pro7
, a.locnam
FROM somelib.REFFILE as a
) WITH DATA
RCDFMT BGTABLEAR;

Not sure how that SQL-how-to topic has anything to do with RPG; i.e. q&a would be the same, irrespective of language. Nonetheless, I offer:

No. The as-result-table syntax has no such capability to include constraint definitions; that parenthetical syntax is mutually-exclusive of the typical parenthetical form of the CREATE TABLE syntax with which constraints can be defined and data-type are explicitly stated vs obtained by reference from other table\view objects.

While none of the following are ideal alternative, each may be able to accomplish what is desired [if implicit journaling is in effect for the CREATE]; • the first requires assigning the data types rather than obtaining them from a reference, • the second creates a VIEW over the reference file with the query from the required DML to generate a view-name for a LIKE-clause reference and for the later INSERT, and • the third requires repeating the DML in both the as-result-table and INSERT [performing the copy in the definitional\CREATE phase is not possible due to a restriction\limitation, still on v7r1 anyhow, for being able to add a constraint while row data is pending-changes\uncommitted]:


BEGIN ATOMIC
CREATE TABLE mylib.BGTABLEA
( mypro7 [data-type] /* no column-level LIKE clause */
, mylocnam [data-type] /* no column-level LIKE clause */
, CONSTRAINT BGTABLEA_PRIMARYKEY
PRIMARY KEY ( mypro7 )
, CONSTRAINT BGTABLEA_CHKMYLOCNAM CHECK ( mylocnam<>' ' )
) RCDFMT BGTABLEAR
;
INSERT INTO mylib.BGTABLEA
( SELECT a.pro7
, a.locnam
FROM somelib.REFFILE as a
)
;
END


BEGIN ATOMIC
CREATE VIEW QTEMP.BGTABLEA_REFFILE
( mypro7
, mylocnam
) as
( SELECT a.pro7
, a.locnam
FROM somelib.REFFILE as a
)
;
CREATE TABLE mylib.BGTABLEA
( LIKE QTEMP.BGTABLEA_REFFILE
, CONSTRAINT BGTABLEA_PRIMARYKEY
PRIMARY KEY ( mypro7 )
, CONSTRAINT BGTABLEA_CHKMYLOCNAM CHECK ( mylocnam<>' ' )
) RCDFMT BGTABLEAR
;
INSERT INTO mylib.BGTABLEA
( SELECT a.*
FROM SESSION.BGTABLEA_REFFILE as a
)
;
DROP VIEW QTEMP.BGTABLEA_REFFILE
;
END


BEGIN ATOMIC
CREATE TABLE mylib.BGTABLEA
( mypro7
, mylocnam
)
as
( SELECT a.pro7
, a.locnam
FROM somelib.REFFILE as a
) WITH NO DATA
RCDFMT BGTABLEAR
;
ALTER TABLE mylib.BGTABLEA
add CONSTRAINT BGTABLEA_PRIMARYKEY
PRIMARY KEY ( mypro7 )
, add CONSTRAINT BGTABLEA_CHKMYLOCNAM
CHECK ( mylocnam<>' ' )
INSERT INTO mylib.BGTABLEA
( SELECT a.pro7
, a.locnam
FROM somelib.REFFILE as a
)
;
END

Note: Regarding the LIKE clause, be sure to review the copy-options; e.g. I do not know what the effect is for referencing a VIEW vs a TABLE.
[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzhctabl.htm]