TOCPREVNEXTINDEX

AcuODBC User's Guide
Version 6.0

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.

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 WHEN directive, as in this example, the following occurs:

If a data item is subordinate to two or more WHEN directives, the following applies:

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.

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.


Acucorp, Inc.
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.
TOCPREVNEXTINDEX
1988-2003 Acucorp, Inc.
All rights reserved.