The WHEN directive is used when you want to include multiple record definitions or REDEFINES for use with your Windows application. It's typically used when you want to force certain columns to be available that wouldn't be available by default (because you want to use them from the Windows application).
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 in one of these columns, even if the data doesn't necessarily make sense. 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 reasonable.
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 you 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, 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 tablename listed as the name of the table.
If you select a named condition 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)
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."
$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 <, <=, >, >=, !=)
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
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.
ship-code-record.
*(( xfd when ar-code-type = other ))
03 obsolete-code-record redefines
ship-code-record.
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
|
Note that if you try to set the first row so that col_type=a, def1=xx, and def2=20, an error will result if you try to update the row.