Chuck:
Thanks for your insights. I only put the request in this forum because I couldn't find a more suitable one and SQLRPGLE questions get asked here all the time.
Bruce Guetzkow
Programmer/Analyst
Ask me why we switched from AS/400 to IBM Power Systems Server,
the same system that runs Jeopardy champ Watson!
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, May 25, 2016 12:12 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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]
--
Regards, Chuck
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxx for any subscription related questions.
midrange.com
