It's My (De)fault That You're a Zero
Published: July 21, 2010
by 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.
- A record (row) is added to (inserted into) a physical file (table) by means of a logical file or SQL view that does not contain one or more fields.
- RPG O specs list the fields of an externally described file, but not all fields are listed.
- An SQL insert command does not include one or more fields in the list of columns.
- New fields are added to the DDS of a physical file and Change Physical File (CHGPF) is used to rebuild the file.
- The Initialize Physical File Member (INZPFM) command is used with the RECORDS(*DFT) parameter to add records to a database file.
The default value of a field is based on three criteria:
- Whether the programmer specified a default value.
- Whether the physical file was created with DDS or SQL.
- The field's data type.
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.
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:
- USER (the current user profile)
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?
Post this story to del.icio.us
Post this story to Digg
Post this story to Slashdot