AcuODBC User's Guide
6.3.11 WHEN Directive
Use the WHEN directive when you want to include multiple record definitions or REDEFINES in the XFD for use with your Windows application. The WHEN directive is typically used to force certain columns of data to be available that wouldn't be available otherwise.
Recall that the key fields and the fields from the largest record are automatically included as explicit columns in the "virtual" database table. So you should use the WHEN directive if you want the user to be able to access all the data in the COBOL file in a way that is understandable.
WHEN declares that the field (or subordinate fields, if it is a group item) that immediately follow the directive must appear as a column (or columns) in the "virtual" database table. It also states one condition under which the columns are to be used. WHEN thus guarantees that the fields will be explicitly included in the table (as long as they aren't FILLER and don't occupy the same area as key fields).
Syntax$XFD WHEN field operator value
or*(( XFD WHEN field operator value ))
Field is the name of a data item that corresponds to a field. If there is a NAME directive for this data item, the name used in the WHEN directive is the name given to the item by the NAME directive, not its COBOL name.
The operator specifies the relation between the field value and the alphanumeric literal that satisfies the condition. Operator can be one of the following:
Operator Relation = The field value is equal to the literal value. != The field value is not equal to the literal value. > The field value is greater than the literal value. < The field value is less than the literal value. >= The field value is greater than or equal to the literal value <= The field value is less than or equal to the literal value
Value is an alphanumeric literal (if the field is alphanumeric) or a numeric literal (if the field is numeric) or the special word "Other". "Other" is used only with the "=" operator:$XFD WHEN field = other
"Other" is true only when all other conditions for the same field are false. For example, if your FD contains the following lines of code$XFD WHEN ATYPE = "C" $XFD WHEN ATYPE = "D" $XFD WHEN ATYPE = other
the "other" condition holds true only if both "atype = 'c'" and "atype = 'd'" are false.
The following code demonstrates an example of using the WHEN directive.01 key-record. * employee-number is a key data item 03 employee-number pic 99999. 03 emp-type pic x. $xfd when emp-type="1" * record has this form when emp-type=1 01 data-record-1. 03 filler pic 99999. 03 filler pic x. 03 name-1 pic x(35). 03 pay-rate-1 pic 99.99. $xfd when emp-type="2" * record has this form when emp-type=2 01 data-record-2 03 filler pic 99999. 03 filler pic x. 03 name-2 pic x(35). 03 pay-rate-2 pic 999.99. 03 subordinates pic 999. 03 position pic x(50).
The effect of these directives is to force "emp-type", "name-1", and "pay-rate-1" to correspond to fields, even though they are not in the largest record description. Therefore, the corresponding table has the following fields:
If each data item is subordinate to at most one WHEN directive, as in this example, the following occurs:
- When the condition is true, the data item is transferred between the COBOL data file and the database table in the usual way.
- When the condition is false, the special value NULL appears in the corresponding field in the database table, and any value written into the field in the database table is not written to the COBOL data file. The exact meaning of a NULL value depends on the database. In some databases, NULL is a blank or zero value. In others, NULL is a special value on which no arithmetic or string operations can be performed, although a value can be tested to determine whether it is NULL.
If a data item is subordinate to two or more WHEN directives, the following applies:
- When all conditions are true, the data item is transferred between the COBOL data file and the database table in the usual way.
- When at least one condition is false, the special value NULL appears in the corresponding field in the database table, and any value written into the field in the database table is not written to the COBOL data file.
WHEN Directive With TABLENAME Clause
A WHEN condition can be assigned a tablename, so that AcuODBC will consider the different conditions as separate tables. If you assign a tablename, the data that immediately follows the WHEN directive and meets the specified condition will be considered as one discrete table. Therefore, you will have the table(s) specified by the WHEN directive plus the table(s) specified by the TABLENAME clause.
If you select a WHEN condition as a table, that table will be made up of all of the columns that depend on the condition. Note that if a condition is not named, that condition will not have a separate table associated with it.
When an XFD names a condition, such as WHEN, AcuODBC produces multiple tables from a single XFD file. One table is given the current name of the file, while the tables resulting from any named conditions are given the name specified with the tablename parameter (see syntax section below).
You cannot include a HIDDEN field in a WHEN directive with a TABLENAME clause, due to the complexities of editing or adding records. In such a situation, you must add the data, but since the field is hidden, you cannot see it and may add a value that would cause unexpected results.
If an XFD file in an alias contains WHEN directives with TABLENAME phrases, the corresponding tables are defined in the usual way, using the data file specified by its physical file name. You can define two or more file aliases with the same XFD file but with different physical file names if the XFD file does not contain any WHEN directives with the TABLENAME clause.
Syntax$XFD WHEN field operator value TABLENAME=new_table_name or *(( XFD WHEN field operator value TABLENAME=new_table_name ))
The syntax is essentially the same as for the WHEN directive alone, with the addition of the TABLENAME clause The word OTHER can be used only with "=". It means "use the following field(s) only if none of the other WHEN condition(s) listed for the same field is met." In other words, this condition is true only if all other conditions for the same field are false.
For example:. . assign to "ar_table" . . 01 ar-code-type. *(( xfd when ar-code-type = "s" tablename=ship )) 03 ship-code-record pic x(4). *(( xfd when ar-code-type = "b" tablename=backorder )) 03 backorder-code-record redefines ship-code-record. *(( xfd when ar-code-type = other )) 03 obsolete-code-record redefines ship-code-record.
If you tried to connect to this data source through a program like Access, you would see three tables: ship, backorder, and ar_table. If you placed $XFD READ-ONLY TABLE immediately before the "xfd when ar-code-type = "s" tablename=ship" line, the ship table and ar_table would be read-only, but the backorder table would not.
OTHER may be used before one record definition, and may be used once at each level within each record definition.
Note: A WHEN directive with condition OTHER must be used if there is a possibility that the data in the field will not meet any of the explicit conditions specified in the other WHEN directives. If this is not done, results are undefined. Also, WHEN directives may ensure that there will be multiple columns that share the same record area. If you try to modify both columns, an error results.
If the following code were compiled without directives, the underlined fields would appear explicitly in the database table. Note that the key fields are included automatically, as are the fields from the largest record. FILLER would be ignored:01 ar-codes-record. 03 ar-codes-key. 05 ar-code-type pic x. 05 ar-code-num pic 999. 01 ship-code-record. 03 filler pic x(4). 03 ship-instruct pic x(15). 01 terms-code-record. 03 filler pic x(4). 03 terms-rate-1 pic s9v999. 03 terms-days-1 pic 9(3). 03 terms-rate-2 pic s9v999. 03 terms-descript pic x(15).
If you added the WHEN directive as shown below, it would cause the fields from the SHIP-CODE-RECORD to be included in the database table, and would determine when specific database columns would be used. The underlined fields would appear as columns in the database table:01 ar-codes-record. 03 ar-codes-key. 05 ar-code-type pic x. 05 ar-code-num pic 999. $xfd when ar-code-type = "s" 01 ship-code-record. 03 filler pic x(4). 03 ship-instruct pic x(15). $xfd when ar-code-type = "t" 01 terms-code-record. 03 filler pic x(4). 03 terms-rate-1 pic s9v999. 03 terms-days-1 pic 9(3). 03 terms-rate-2 pic s9v999. 03 terms-descript pic x(15).
FILLER data items don't have unique names and are thus not used to form columns in the database table. You could use the NAME directive to give them a name if you really need to see them in the database table. However, in this example the FILLER data items implicitly redefine key fields. Thus, they would be disregarded even if you provided a name for them.
In the following code, in which no WHEN directives are used, the underlined fields will be explicitly named in the database table. (Key fields have the suffix "key" in their names in this example.)
Note that REDEFINES records simply re-map the same data area and are not explicitly included in the database table by default:01 archive-record. 03 filler pic x(33). 03 archive-code pic 9(6). 03 archive-location pic 9(2). 03 filler pic x(10). 01 master-record. 03 animal-id-key. 05 patient-id pic 9(6). 05 species-code-type pic 9(5). 05 species-name pic x(6). 03 service-code-key. 05 service-code-type pic 9(6). 05 service-name pic x(10). 03 billing-code. 05 billing-code-type pic 9(4). 05 plan-name pic x(8). 03 office-info. 05 date-in-office pic 9(8). 05 served-by-name pic x(10). 03 remote-info redefines office-info. 05 van-id pic 9(4). 05 proc-code pic 9(8). 05 vet-name pic x(6).
If you added the WHEN directives shown below, you would add several columns to the database table. The fields that would appear in the table are underlined:*(( xfd when animal-id-key = "00000000000000000" )) 01 archive-record. 03 filler pic x(33). 03 archive-code pic 9(6). 03 archive-location pic 9(2). 03 filler pic x(10). *(( xfd when animal-id-key = other )) 01 master-record. *(( xfd use group )) 03 animal-id-key. 05 patient-id pic 9(6). 05 species-code-type pic 9(5). 05 species-name pic x(6). 03 service-code-key. 05 service-code-type pic 9(6). 05 service-name pic x(10). 03 billing-code. 05 billing-code-type pic 9(4). 05 plan-name pic x(8). *(( xfd when billing-code-type = "1440" )) 03 office-info. 05 date-in-office pic 9(8). 05 served-by-name pic x(10). *(( xfd when billing-code-type = other )) 03 remote-info redefines office-info. 05 van-id pic 9(4). 05 proc-code pic 9(8). 05 vet-name pic x(6).
If your application has a REDEFINES whose field names are more meaningful than the fields they redefine, you might consider switching the order of your code, rather than using a WHEN directive. Use the less significant field names in the REDEFINES.
For example, you might change this:03 code-info. 05 filler pic 9(8). 05 code-1 pic x(10). 03 patient-info redefines code-info. 05 patient-id pic 9(4). 05 service-code pic 9(8). 05 server-name pic x(6).
to this:03 patient-info. 05 patient-id pic 9(4). 05 service-code pic 9(8). 05 server-name pic x(6). 03 code-info redefines patient-info. 05 filler pic 9(8). 05 code-1 pic x(10).
The fields that would appear in the database table by default are underlined above. This shows how the column names might become more meaningful when the order is reversed. Your application operates the same either way.
Note: If a WHEN condition is false for a particular record, columns corresponding to data items subject to the WHEN directive and in the row corresponding to the record are set to the special database value NULL This means that there is no value provided for those columns. NULL is not equivalent to zero, and it has special properties in the database. For example, you can select all rows for which a given column is NULL.
This COBOL code:01 col-type pic x. 03 col-def. $xfd when col-type = "a" 05 def1 pic x(2). $xfd when col-type = "b" 05 def2 redefines def1 pic 9(2).
results in this database table:
Note that if you try to set the first row so that col_type=a, def1=xx, and def2=20, the value of def2 is not stored.
Voice: (800) 262-6585 (U.S.A. and Canada)
Voice: (858) 689-4500
Fax: (858) 689-4550
Please share your comments on this manual
or on any Acucorp documentation with the
Acucorp Communications Department.
|© 1988-2003 Acucorp, Inc.|
All rights reserved.