RE: SQL CREATE TABLE command - ALIAS (Joe Pluta)

Joel, 
just curious, do you have an example of an ALTER TABLE which demonstrates
setting the short field name (EX:       contractor_id for contrac_id int not
null with default(0)).

I can see it done in CREATE TABLE but your code comments imply that ALTER
TABLE has the same function, and I can't make it work.

Elvis

-----Original Message-----


        System Names:
        ============
        SQL on the iSeries supports long names, but many programs and
queries
still use the system names which are limited to 10 characters.
        When a long name occurs with no defined system name, the name is
truncated to five characters and incremented (within the table).
        I.E. "Contractor_Type" = "CONTR00001", "Contractor_Address_1" =
"CONTR00002", etc.

        To avoid these cryptic names and still be able to take advantage of
SQLs long name support, be sure to add a system name for any
        field name longer than 10 characters.  This is done in the "Create
Table" or "Alter Table" statements by adding a "... for XXX ..." clause
        to the field description:

        EX:     contractor_id for contrac_id int not null with default(0)

        The SQL name - "contractor_id" and the System Name is "CONTRAC_ID"
(uppercases will be added by the system)

*/
/*======================================================================
   PermitDirections
  ======================================================================
        LABEL           MASIBP Building Permit directions table.

        COMMENT Provides Directions to the physical location of the
property.

        MODIFICATION HISTORY:
        date                    user            comment
        --------------  ---------
-------------------------------------------------------------
        06-17-2004      JRC             Initial Creation

        KEY FIELDS:
        Primary Key     permit_id, seq_no
        Foreign Keys    permit_id - permit_id from MASIBP.PermitMaster

        ADDITIONAL NOTES:
*/
create table masibp.PermitDirections
( permit_id int not null ,
  seq_no int not null ,
  directions char(50) not null ,
  last_change_user for chgUser char(10) not null ,
  last_change_timestamp for chgStamp timestamp not null with default ,
  Primary Key( permit_id, seq_no )
);

/* Descriptive Table Text */
label on table masibp.PermitDirections
  Is 'MASIBP Building Permit directions.'
;

comment on table masibp.PermitDirections
  Is 'Provides Directions to the physical location of the property.'
;

/* Table Column Headers */
label on masibp.PermitDirections
( permit_id                             is      'Permit ID' ,
  seq_no                                is      'Sequence Number' ,
  directions                            is      'Directions' ,
  last_change_user              is      'Last Changed        User ID' ,
  last_change_timestamp is      'Last Changed        Timestamp'
);

/* Descriptive Column Text */
label on masibp.PermitDirections
( permit_id                             text is 'Permit ID' ,
  seq_no                                text is 'Sequence Number' ,
  directions                            text is 'Directions' ,
  last_change_user              text is 'Last Changed User ID' ,
  last_change_timestamp text is 'Last Changed Timestamp'
);

/* Set the System Name for the Table */
rename table masibp.PermitDirections
  To System Name PermDirect
;

</CODE>

HTH,

Joel Cochran
http://www.rpgnext.com