## DEMO CUST MAST TABLE ## HAS CUSTOMER NUM,NAME,ADDRESS ## ALSO HAS 3-char region ## 3-char class CREATE TABLE IF NOT EXISTS DCUST_MAST ( DCMST_NUM char(3) NOT NULL default '', DCMST_NAME char(30) default NULL, DCMST_ADRS char(30) default NULL, DCMST_CITY char(20) default NULL, DCMST_STATE char(2) default NULL, DCMST_ZIP char(10) default NULL, DCMST_REGION char(4) NOT NULL default '0000', DCMST_CREATED datetime default NULL, DCMST_TIME timestamp(14) NOT NULL, DCMST_WHO char(20) default NULL, DCMST_CLASS char(3) default 'USA', PRIMARY KEY (DCMST_NUM) ) TYPE=ISAM PACK_KEYS=1 ; ## DEMO CUST MAST LOGTABLE ## HAS STANDARD FIELD NAMING ## STANDARD ADDED LOG FIELDS ## 3-ch CREATE TABLE IF NOT EXISTS DCUST_MAST_LOG ( DCMST_NUM_LOG char(3) , DCMST_NAME_LOG char(30) , DCMST_ADRS_LOG char(30) , DCMST_CITY_LOG char(20) , DCMST_STATE_LOG char(2) , DCMST_ZIP_LOG char(10) , DCMST_REGION_LOG char(4) , DCMST_CREATED_LOG datetime , DCMST_TIME_LOG timestamp(14) , DCMST_WHO_LOG char(20) , DCMST_CLASS_LOG char(3) , DCMSTLOG_CREATED datetime , DCMSTLOG_WHO char(20) , DCMSTLOG_DESC char(60) ) TYPE=ISAM PACK_KEYS=1 ; select IF(@NUM is null, @NUM:=1, @NUM:=@NUM+1); INSERT INTO DCUST_MAST (DCMST_NUM,DCMST_NAME,DCMST_ADRS,DCMST_CITY,DCMST_STATE, DCMST_ZIP,DCMST_REGION,DCMST_CREATED,DCMST_TIME,DCMST_WHO,DCMST_CLASS) VALUES (LPAD(CONCAT('0',@NUM),3,'0'),CONCAT('CUST ',LPAD(@NUM,3,'0')), CONCAT('ADDRESS ',LPAD(@NUM,3,'0')),CONCAT('CITY',LPAD(@NUM,3,'0')),'WI',CONCAT('53',LPAD(@NUM,3,'0') ),'000',NOW() ,NOW(),USER(),'000');