1 Field_attr The characteristics of a field. (B)0field-attributes = DATATYPE IS qqq> data-type qqk lqqqqqqqqqqqqqqqqqqqqqqqqqj mqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqq> mwqwqq> validity-clauseqqqqqqqqqqqqqqqqqqqqqqqwqwqj x tqq> missing-value-clauseqqqqqqqqqqqqqqqqqqu x x tqq> dtr-clauseqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x x tqq> COLLATING_SEQUENCE IS sequence-nameqqqu x x mqq> NO COLLATING_SEQUENCE qqqqqqqqqqqqqqqqj x mqqqqqqqqqqqqqqqqq SEGMENT_LENGTH IS qqq> n qqq> BYTES qqwqwq> x mqqq> SUB_TYPE IS qqqqwqqqq> BLR qqqqqqwqqqqj x x tqqqq> TEXT qqqqqu x x mqqqq> n qqqqqqqqj x mqqqqqqqqqqqqqqqqqqqqqq VALID IF qqq> conditional-expr qwq> mq> NO VALID IF qqqqqqqqqqqqqqqqqqqqj conditional-expr Specifies a valid conditional expression. This conditional expression must refer only to the field being defined. Ask for HELP on Cond_ expr for a complete description of Oracle Rdb conditional expressions. 3 Example In the following example, the SHOW FIELD statement shows the SEX field, which contains a VALID IF clause. The CHANGE FIELD statement removes the VALID_IF clause from the SEX field: RDO> DATA FILE DB$:PERSONNEL RDO> SHOW FIELD SEX SEX text size is 1 Description: M, F Missing value: "?" Valid: IF SEX = 'M' OR SEX = 'F' OR SEX MISSING RDO> CHANGE FIELD SEX NO VALID IF. RDO> SHOW FIELD SEX SEX text size is 1 Description: M, F Missing value: "?" RDO> ROLLBACK 2 missing-value-clause Specifies a numeric or character string literal that denotes that no value is stored in the field. When you retrieve or display the value, Oracle Rdb returns the contents of the MISSING_VALUE string or number. (B)0missing-value-clause = qqqqq> MISSING_VALUE IS qqqwqqq> fxd-pnt-num qqqqqwqqq> mqqq> quoted-string qqqj 3 fxd-pnt-num A fixed-point number that Oracle Rdb uses to replace a retrieved null value. A fixed-point number always includes a decimal point, even when there are no digits to the right of the decimal point. For example, if a field contains dollar and cent values, you may want to use 0.00 as the missing value to preserve the value context when Oracle Rdb retrieves and displays null values. In this particular case, if you do not allow null values and you do not specify a missing value, Oracle Rdb fills the retrieved field with zeros. Specify a number that does not exceed the defined field length and include a decimal point. 3 quoted-string A character string that Oracle Rdb uses to replace retrieved null values. For example, you could specify a missing value of "Value not found" to replace null values. Specify a character string whose length does not exceed the length of the field. Enclose the string in single or double quotation marks ("string" or 'string'). Ask for HELP on Value_expr for a complete description of the use of literal expressions. 3 Examples Example 1 Define missing value for dates: DEFINE FIELD STANDARD_DATE DESCRIPTION /* Universal date field */ DATATYPE IS DATE MISSING_VALUE IS "17-NOV-1858 00:00:00.00" EDIT_STRING FOR DATATRIEVE IS 'DD-MMM-YYYY'. This definition defines a missing value for date fields to be the same as the base date and time for OpenVMS. Example 2 Define missing values for numeric fields: DEFINE FIELD SALARY DATATYPE IS SIGNED LONGWORD SCALE -2 MISSING_VALUE IS 0.00 EDIT_STRING FOR DATATRIEVE IS '$$$$,$$9.99'. This statement specifies "0.00" as the missing value for SALARY fields. Example 3 Define missing values for text fields: DEFINE FIELD ADDRESS_DATA DATATYPE IS TEXT SIZE 20 MISSING_VALUE IS "Not available". When you retrieve a field whose value is missing, Oracle Rdb displays the string "Not available". 2 dtr-clause Oracle Rdb provides support for DATATRIEVE, the Digital query language. You can specify one or more of the following clauses: Query header Query name Default value Edit string If you specify a DATATRIEVE attribute on a global field, you can cancel that attribute for a particular relation, using the NO qualifier. The NO qualifier of the dtr-clause is available when you are changing a definition with the CHANGE FIELD or CHANGE RELATION statement. The NO qualifier is not used with the dtr- clause of DEFINE FIELD, DEFINE RELATION, and DEFINE VIEW. 3 Format (B)0dtr-clause = qwq> QUERY_HEADER FOR qqwq> DTR qqqqqqqqwqq> IS qw> quoted-string qqwqwq> x mq> DATATRIEVE qj mqqqqqq / NO QUERY_HEADER FOR qqwqq DTR qqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqu x mq> DATATRIEVE qqqj x tq> QUERY_NAME FOR qqqqwq> DTR qqqqqqqqqwq> IS qq> quoted-string qqqqu x mq> DATATRIEVE qqj x tq> NO QUERY_NAME FOR qqqqwqq DTR qqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqu x mq> DATATRIEVE qqj x tq> DEFAULT_VALUE FOR qwq> DTR qqqqqqqqqwq> IS qw> fxd-pnt-num qqqqwqu x mq> DATATRIEVE qqj m> quoted-string qqj x tq> NO DEFAULT_VALUE FOR qwq> DTR qqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqu x mq> DATATRIEVE qqj x tq> EDIT_STRING FOR qqqwq> DTR qqqqqqqqqwq> IS qq> quoted-string qqqqu x mq> DATATRIEVE qqj x mq> NO EDIT_STRING FOR wq> DTR qqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqj mq> DATATRIEVE qqj fxd-pnt-num The default value returned to DATATRIEVE when Oracle Rdb retrieves null values. A fixed-point number is a number that always includes a decimal point, even when there are no digits to the right of the decimal point. quoted-string A literal expression that Oracle Rdb returns to DATATRIEVE. 3 Examples Example 1 The following example defines a field and specifies a DATATRIEVE edit string for that field: DEFINE FIELD MIDDLE_INITIAL DATATYPE IS TEXT SIZE 1 EDIT_STRING FOR DATATRIEVE IS 'X.'. The X represents any alphanumeric character. When you access this field through DATATRIEVE, the field value is followed by a period. The DATATRIEVE documentation set contains a complete list of editing characters. Example 2 The following example defines a DATATRIEVE query name for one field and a DATATRIEVE query header for another: DEFINE FIELD STATE DATATYPE IS TEXT SIZE 2 QUERY_NAME FOR DATATRIEVE IS "ST". DEFINE FIELD SEX DATATYPE IS TEXT SIZE 1 VALID IF SEX = 'M' OR SEX = 'F' QUERY_HEADER FOR DATATRIEVE IS "S"/"E"/"X". These statements define DATATRIEVE query names and query headers for the STATE and SEX fields. The header for the SEX field will be one character wide, like the field itself. 2 COLLATING The COLLATING_SEQUENCE clause specifies the collating sequence to be used in the field you are defining. The NO COLLATING_SEQUENCE clause specifies that the field will use the standard default collating sequence (the ASCII collating sequence).