Re: How to do REFFLD with DDL

Column defined in "field reference table":

create table ref-table-name ...
primary_key_oid for pk_oid integer not null generated always
as identity
(cycle) ...



Column defined in a table using the reference table:

create table table-name
( priKey , ... )
as ( select primary_key_oid , ...
from ref-table-name )
definition only
including identity column attributes
including column defaults


On 9/26/07, David Foxwell <david.foxwell@xxxxxxxxx> wrote:

Thanks Matt, that helps but I couldn't create a colomn as IDENTITY that way.
----- Original Message -----
From: <matt.haas@xxxxxxxxxxx>
To: <rpg400-l@xxxxxxxxxxxx>
Sent: Wednesday, September 26, 2007 1:14 PM
Subject: RE: How to do REFFLD with DDL


If you're on V5R2 or newer, you can do this. Take a look at
http://www.itjungle.com/mgo/mgo062703-story02.html. This also shows how to
define a column that isn't in the reference file.

Matt

________________________________

From: rpg400-l-bounces@xxxxxxxxxxxx on behalf of David Foxwell
Sent: Wed 9/26/2007 5:27 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: How to do REFFLD with DDL



Hi,

All our files are plain old DDS PFs and LFs. Every field is referenced in an
empty PF called DICTIONARY or something like that. It contains around 2500
field names.Any new file must use REFFLD on all its fields.


Just received the order to create 2 SQL tables, (don't ask me why). Each
zone must still be based on DICTIONARY.
Just to make it more interesting it must have a primary key (not a DDS
unique key).

I've tried
CREATE TABLE AS ( select fieldname from DICTIONARY )

followed by
ALTER TABLE ADD COLUMN fieldname ROWID.


How can I put the ROWID column top of the ist of fields?
How can I get back the column headings that I would have had if I'd used
DDS?

Any other ideas?


--
This is the RPG programming on the AS400 / 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.






----------------------------------------------------------------------------
----

--
This is the RPG programming on the AS400 / 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.

--
This is the RPG programming on the AS400 / 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.