Below is an IBM i SQL statement to create a business customer address file. This example assumes a table structure suitable for storing business customer address information, including common fields like customer ID, company name, address details, and contact information. The table will be created in a specified library (schema) with appropriate constraints and attributes for an IBM i environment.
CREATE TABLE MYLIB/CUSTADDR (
CUSTID CHAR(10) NOT NULL,
COMPNAME VARCHAR(100) NOT NULL,
ADDR1 VARCHAR(50),
ADDR2 VARCHAR(50),
CITY VARCHAR(50),
STATE CHAR(2),
ZIPCODE CHAR(10),
COUNTRY VARCHAR(50),
CONTACTNAME VARCHAR(100),
PHONE CHAR(15),
EMAIL VARCHAR(100),
CREATED_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (CUSTID)
)
RCDFMT CUSTADDRR;
CUSTID CHAR(10) NOT NULL,
COMPNAME VARCHAR(100) NOT NULL,
ADDR1 VARCHAR(50),
ADDR2 VARCHAR(50),
CITY VARCHAR(50),
STATE CHAR(2),
ZIPCODE CHAR(10),
COUNTRY VARCHAR(50),
CONTACTNAME VARCHAR(100),
PHONE CHAR(15),
EMAIL VARCHAR(100),
CREATED_DATE TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
LAST_UPDATED TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (CUSTID)
)
RCDFMT CUSTADDRR;
Explanation:
- CUSTID: A unique identifier for the customer (CHAR(10), primary key).
- COMPNAME: Company name (VARCHAR(100), required).
- ADDR1, ADDR2: Address lines (VARCHAR(50), optional).
- CITY, STATE, ZIPCODE, COUNTRY: Address components.
- CONTACTNAME, PHONE, EMAIL: Contact details.
- CREATED_DATE, LAST_UPDATED: Timestamps for tracking record creation and updates.
- NOT NULL ensures required fields are populated.
- PRIMARY KEY (CUSTID) enforces uniqueness for the customer ID.
- CHAR for fixed-length fields like CUSTID, STATE, ZIPCODE, and PHONE.
- VARCHAR for variable-length fields like company name and address lines.
- TIMESTAMP for date tracking.
Other Notes:
If you have specific requirements (e.g., additional fields, indexes, or journaling), let me know, and I can tailor the statement further!