The WHEN directive is used when you want to include multiple record definitions or REDEFINES in the XFD for use with your Windows application. It's typically used when you want 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. All COBOL data will be forced into tabular columns, and the organization of these columns is arbitrary. 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).
A WHEN condition can also be assigned a tablename, so that AcuODBC will consider the different conditions as separate tables. If you assign a tablename, then the data that immediately follows the WHEN directive and meets the specified condition will be considered as a separate table.
When an XFD names conditions, such as WHEN or WHERE, AcuODBC presents multiple tables from a single XFD file. The tables include the current name of the file, as well as any named conditions, with a tablename parameter listed as the name of the table (see syntax section below).
If you select one of these conditions as a table, the columns of that table will consist of any column that depends on that condition. Note that every condition depends on condition 0, so every column that uses condition 0 will be included in the table. The rows of the table will consist of all rows for which the condition is true. Note that if a condition is not named, then that condition will not have a table associated with it.
Syntax
$XFD WHEN field=value TABLENAME=name (equals) $XFD WHEN field=OTHER TABLENAME=name (equals) $XFD WHEN field<=value TABLENAME=name (is less than or equals) $XFD WHEN field<value TABLENAME=name (is less than) $XFD WHEN field>=value TABLENAME=name (is greater than or equals) $XFD WHEN field>value TABLENAME=name (is greater than) $XFD WHEN field!=value TABLENAME=name (is not equal to) or *(( XFD WHEN field=value TABLENAME=name)) (also <, <=, >, >=, !=)
The value may be an explicit data value (in quotes). The word OTHER can be used only with "=". It means "use the following field(s) only if the WHEN condition(s) listed at the level of this field are not met."
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 COMMENT ACUODBC READ-ONLY TABLE immediately before the "xfd when ar-code-type = "s" tablename=ship" line, then 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.
If the following code were compiled without directives, the underlined fields would appear explicitly in the database table. Note that the key fields would be included automatically, as would 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.
Example 2
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).
Example 3
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.
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).
will result in this database table:
| col_type
| def1
| def2
|
| a
| xx
| null
|
| b
| null
| 10
|
| a
| yy
| null |