RE: DDS to SQL - date definition

["Jeff Crosby" <jlcrosby@xxxxxxxxxxxxxxxx>]

Here's the 2nd file I tested, since it's smaller.  Note that I haven't
cleaned everything up yet, like LABEL ON for all fields:

DML:

/*    *_> CNLLSTSPLF SRCFILE(@2/@1) SRCMBR(@3)                  */
/*    *_> RUNSQLSTM SRCFILE(@2/@1) SRCMBR(@3) COMMIT(*NC)       */

--  Generate SQL
--  Version:                   V5R4M0 060210
--  Generated on:              06/19/07 09:26:55
--  Relational Database:       DILGARD
--  Standards Option:          DB2 UDB iSeries

DROP TABLE JEFF/DMEQPMST ;

CREATE TABLE JEFF/DMEQPMST (
  RCDCD      CHAR(2) CCSID 37   NOT NULL DEFAULT '' ,
             CONSTRAINT eqRcdCd      CHECK (RCDCD = 'EQ') ,
  ACREC      CHAR(1) CCSID 37   NOT NULL DEFAULT '' ,
             CONSTRAINT eqActRec     CHECK (ACREC IN ('A','D','S')) ,
  EQPNR      NUMERIC(5, 0)      NOT NULL DEFAULT 0 ,
  EQSER      CHAR(13) CCSID 37  NOT NULL DEFAULT '' ,
  EQDSC      CHAR(30) CCSID 37  NOT NULL DEFAULT '' ,
             CONSTRAINT eqEqpDsc     CHECK (EQDSC <> ' ') ,
  EQVLU      DECIMAL(7, 2)      NOT NULL DEFAULT 0 ,
  CUSNR      NUMERIC(4, 0)      NOT NULL DEFAULT 0 ,
  EQDTI      DATE               NOT NULL DEFAULT '0001-01-01' ,
  EQDTP      DATE               NOT NULL DEFAULT '0001-01-01' ,
  EQCLS      NUMERIC(2, 0)      NOT NULL DEFAULT 0 ,
  EQDTR      DATE               NOT NULL DEFAULT '0001-01-01' ,
  EQLDS      CHAR(30) CCSID 37  NOT NULL DEFAULT '' ,
  QTYOH      DECIMAL(5, 0)      NOT NULL DEFAULT 0 ,
  PRIMARY KEY( EQPNR ) )
  NOT VOLATILE
  RCDFMT DMEQPRC    ;

LABEL ON TABLE JEFF/DMEQPMST
  IS 'Equipment master' ;

LABEL ON COLUMN JEFF/DMEQPMST
( RCDCD      IS 'Record              Code' ,
  ACREC      IS 'Active              Record              Code' ,
  CUSNR      IS 'Cust                Number' ,
  EQDTI      IS 'Date                Eqp                 Installed' ,
  EQDTP      IS 'Date                Eqp                 Purchased' ,
  EQDTR      IS 'Date                Last                Receipt' ) ;

LABEL ON COLUMN JEFF/DMEQPMST
( RCDCD      TEXT IS 'Record code' ,
  ACREC      TEXT IS 'Active record code. A or D or S' ,
  EQPNR      TEXT IS 'Beverage equipment ID number' ,
  EQSER      TEXT IS 'Beverage equipment serial number' ,
  EQDSC      TEXT IS 'Beverage equipment description' ,
  EQVLU      TEXT IS 'Beverage equipment value' ,
  CUSNR      TEXT IS 'Customer number' ,
  EQDTI      TEXT IS 'Beverage equipment date installed (at CUSNR)' ,
  EQDTP      TEXT IS 'Beverage equipment date purchased by Dilgard' ,
  EQCLS      TEXT IS 'Equipment class' ,
  EQDTR      TEXT IS 'Beverage equipment date last receipt' ,
  EQLDS      TEXT IS 'Beverage equipment location description' ,
  QTYOH      TEXT IS 'Quantity on hand' ) ; 


DDS:

     A                                      UNIQUE
     A                                      REF(FLDREFF)
     A          R DMEQPRC                   TEXT('Equipment Master')
     A            RCDCD     R
     A            ACREC     R
     A            EQPNR     R
     A            EQSER     R
     A            EQDSC     R
     A            EQVLU     R
     A            CUSNR     R
     A            EQDTI     R
     A            EQDTP     R
     A            EQCLS     R
     A            EQDTR     R
     A            EQLDS     R
     A            QTYOH     R
     A          K EQPNR

Relevant date fields from FLDREFF:

     A            EQDTI           L         TEXT('Beverage equipment date
inst+
     A                                        alled (at CUSNR)')
     A                                      DATFMT(*ISO)
     A                                      COLHDG('Date' 'Eqp' 'Installed')
     A            EQDTP           L         TEXT('Beverage equipment date
purc+
     A                                        hased by Dilgard')
     A                                      DATFMT(*ISO)
     A                                      COLHDG('Date' 'Eqp' 'Purchased')
     A            EQDTR           L         TEXT('Beverage equipment date
last+
     A                                         receipt')
     A                                      DATFMT(*ISO)
     A                                      COLHDG('Date' 'Last' 'Receipt')