× 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.



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.