SQL Field Reference File

Hey, Ted:

I've noticed that a lot of Midrange Guru tips have to do with SQL. SQL is fine for a query language, but we're not using it as a data-definition language for one big reason.

The reason we don't use SQL to define tables is that there is no support for a field reference file, as there is in DDS. We like the idea of having the definitions of common fields in a file, where we can reference them when creating other tables.

--Dave

If you are at V5R2, or can get there, you can define a table based on the attributes of other tables.

For example, here's the definition for the field reference file, FIELDREF.

create table mylib/fieldref (
   CompanyNo     dec (3,0),   
   CustomerNo    dec (5,0),   
   AddressLine   char (25),   
   City          char (16),   
   State         char (2),    
   ZipCode       char (10),   
   PhoneNo       char (13))   

V5R2 allows you to select columns from one or more tables when creating a new table. Here's the SQL statement that creates a table named CUSTOMERS.

create table mylib/Customers as         
   (select CompanyNo, CustomerNo,        
           AddressLine as Address1,      
           AddressLine as Address2,      
           City, State, ZipCode,         
           decimal(0,7,2) as CreditLimit,
           PhoneNo as Phone,             
           PhoneNo as Fax                
      from mylib/fieldref)              
   definition only                       

All but one field was defined in the field reference file. The credit limit field had no counterpart in the field reference file, so I defined it with the decimal function.

The term "definition only," in the last line, tells SQL that no data is to be copied from the FIELDREF table. The alternative is to use the term "with data," which tells SQL to load the new table with data from the select. In this case, it doesn't matter which one you use, since there is no data in the field reference file.

--Ted

Sponsored By COMMON

COMMON Fall 2003 Conference and Expo IT in the Palm of Your Hand September 7 - 11, in Orlando, Florida. · Education: 700 sessions in four days · Networking: Meet more than 3,000 IT professionals, IBM executives, and developers · Influence IBM: Attend sound-off sessions · First-Hand Look at Latest Products: Talk to the industry's top solution providers COMMON is the best value in IT education, so don't miss out! Click and visit www.common.org for details!