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

> date: Fri, 22 Jul 2005 07:45:53 -0500
> from: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
> subject: RE: SQL CREATE TABLE command - ALIAS
> 
> Thanks, Birgitta!
> 
> It turns out this is not compatible with any other database, though.  Is
> there a way to set those names later?  Maybe create the table with the
> long name, then later ALTER the column to have a short and long name?
> 
> I can work around it, but I am trying to write machine-independent
> routines and it's pretty difficult.
> 
> Joe

Joe, 

Yes, this is the way I do it.  Below is some standard internal
documentation and then an example of a CREATE TABLE block that we use. 
These came right out of a production text file:

<CODE>

/* Database Generation Document for MASIBP

        Template Checklist for each table:
        ==========================
        1. Documentation
        2. "Create Table" statement
        3. "Label On Table" statement (up to 50 characters) - Provides a short
description
        4. "Comment On Table" statement (up to 2000 characters) - Allows
detailed comments about the table.
        5. "Label On" statement 1 - Established table column headers (up to 60
characters).  Headers are displayed in 20 character increments.
        6. "Label On" statement 2 (uses "text is" instead of "is") - Allows
detailed comments about the column (up to 2000 characters).
        7. "Rename Table xxx To System yyy" - Renames the table to an iSeries
acceptable System name (up to 10 characters).

        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