It’s My (De)fault That You’re a Zero
July 21, 2010 Ted Holt
Unless you say otherwise, numeric database fields have a value of zero and character fields are blank, right? Not necessarily. There’s more to default field values than some i Gurus realize. A field’s default value is the value the system assigns to the field when an application does not specify a value. Default values come into play in several situations.
The default value of a field is based on three criteria:
If you use an SQL command, such as CREATE TABLE, to create a new physical file, fields are null by default. If you use DDS, however, fields are only null-capable if you add the ALWNULL keyword. For non-nullable fields, default values are zero for numeric fields, blanks for character fields, and current values for date/time/timestamp fields. If you’re interested, here’s something to try. Create a physical file with fields of different data types: A R DFTVALREC A CHAR 3A A PACKED 7P 2 A ZONED 3S 0 A DATE L A TIME T A STAMP Z Add one record to the file. INZPFM FILE(SOMEFILE) RECORDS(*DFT) TOTRCDS(1) Query the file to see the values in the record. Those are the default values. ....+....1....+....2....+....3....+....4....+....5....+....6....+....7... CHAR PACKED ZONED DATE TIME STAMP .00 0 2010-07-21 07.38.59 2010-07-21-07.38.59.766261 To assign your own default values, use the DFT keyword in DDS and WITH DEFAULT in SQL. A R DFTVALREC A CHAR 3A DFT('ABC') A PACKED 7P 2 DFT(250) A ZONED 3S 0 DFT(-1) A DATE L DFT('2000-01-01') A TIME T DFT('00.00.00') A STAMP Z DFT('2000-12-31-24.00.00') create table somefile ( KeyField dec (5,0), CharField char (3) with default 'XYZ', PackedField dec (7,2) with default 1.5) In addition to literal values, SQL lets you insert special values:
And if you want the default value to be null, you can make it happen, whether you use DDS or SQL. Make it a habit to think about default values when you create new physical files (or SQL tables). For instance, shouldn’t a sales order be a regular order by default, rather than a special order?
|