× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.