6.3.11 WHEN Directive

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. 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

*(( XFD WHEN field operator value ))

Field is the name of a data item that corresponds to a field. If there is an XFD NAME directive for this data item, the name used in the XFD WHEN directive is the name given to the item by the XFD NAME directive, not its COBOL name.

The operator specifics 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

The 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.

Example

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:

EMPLOYEE_NUMBER
TYPE
NAME_1
PAY_RATE_1
NAME_2
PAY_RATE_2
SUBORDINATES
POSITION

If each data item is subordinate to at most one XFD WHEN directive, as in this example, for such a data item:

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 XFD 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 also 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 a separate table. Therefore, you will have the tables specified by the WHEN directive plus the tables specified by the TABLENAME clause.

If you select one of these conditions as a table, the columns of that table will consist of all columns that depend on that condition. Note that if a condition is not named, that condition will not have a table associated with it.

When an XFD names a condition, such as WHEN, 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).

You cannot include a HIDDEN field in a WHEN directive with a TABLENAME clause. This is due to the complexities of editing or adding records. With a hidden field, you must add the data, but since the field is hidden, you cannot see it and you might 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=name_of_new_table
or
*(( XFD WHEN field operator value TABLENAME=name_of_new_table ))

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 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.

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.

Example 1

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.

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.

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.

Example 4

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:

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, the value of def2 is not stored.

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