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]
As an Amazon Associate we earn from qualifying purchases.