Re: SQL long column names (was: Re: SQL -- Steve Ballmer says IBM is not innovating lately )

> Not sure you have to rename your fields to use your SQL table in RPG... 
> What about that:
> 
> Create Table MyLib/MyFile (
> My_Product_Code                 For Column PRDCODE Numeric(5,0),
> My_Product_Desc                 For Column PRDDESC Char(25),
> Primary Key (My_Product_Code) );

And the "Column" is optional, just using "For" by itself will work as
well.

If you did NOT use "For" to specify a system name, then the field names
will be truncated to 10 characters as necessary.  For instance, the two
fields above would become:

MY_PR00001
MY_PRO0002

This is especially nasty because of the "O" running into the zeros.  And
of course, you have NO idea from the field name what they are.  The
system will create these little gems for you automatically if you do not
use the "For" clause.  This is not as big an issue in a program with
embedded SQL because you can refer to the long field names no problem. 
It is a tremendously ugly problem if you are using native file access or
if you use the filename for an External DS definition.

When we use SQL to create tables, we have a semi-standard set of
statements we use.  Here is a snippet from a text file that we use in an
AdHoc tool to generate a table on the iSeries:

<CODE>
/*======================================================================
   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