Micro Focus Studio Enterprise Edition for Windows > Programming > Data Access > Micro Focus Database Connectors Version 2.0 for Windows > Database Connectors Reference > XFD Directives > WHEN XFD directive

WHEN XFD directive

The WHEN XFD directive is used when you want to include multiple record definitions or REDEFINES in a database table. It's typically used when you want to force certain columns to be built that wouldn't be built by default (because you want to use them from the RDBMS side). For more information, see the section "REDEFINES" in the chapter XFDs.

Recall that the key fields and the fields from the largest record are automatically included as explicit columns in the database table. All fields are stored and retrieved from the database, whether they appear as explicit columns or not. So you don't need to use WHEN unless you want to ensure that some additional fields are explicitly listed by name in the table. (For details about how fields are included in the database table, see the section "How XFDs Are Formed" in the chapter XFDs.)

WHEN declares that the field (or subordinate fields, if it is a group item) that immediately follow the XFD directive must appear as a column (or columns) in the database table. It also states one condition under which the columns are to be used. The WHEN XFD directive guarantees that the fields are explicitly included in the table, as long as they aren't FILLER and don't occupy the same area as key fields.

The WHEN XFD directive results in a table that unites all fields subordinate to the WHEN XFD directive. This table might include more columns and could affect performance and storage requirements.

Syntax

$XFD WHEN field = value     (equals)
$XFD WHEN field <= value    (is less than or equals)
$XFD WHEN field < value     (is less than)
$XFD WHEN field >= value    (is greater than or equals)
$XFD WHEN field > value     (is greater than)
$XFD WHEN field != value    (is not equal to)
$XFD WHEN field = OTHER

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.

Note: WHEN values must be on individual lines as single values in the code. WHEN values cannot be combined.

For example:

     03 ar-code-type                  pic x. 
$xfd when ar-code-type = "s" 
     03 ship-code-record              pic x(4). 
$xfd when ar-code-type = "b" 
     03 backorder-code-record redefines 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.

Note: A WHEN XFD directive with condition OTHER must be used if there is a possibility that the data in the field does not meet any of the explicit conditions specified in the other WHEN XFD directives. If this is not done, results are undefined.

Example 1

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 add the WHEN XFD directive as shown below, it causes the fields from the SHIP-CODE-RECORD to be included in the database table, and determines when specific database columns are used. The underlined fields then 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 XFD directive to give them a name if you really need to see them in the database table.

Note: 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 XFD directives are used, the underlined fields are 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 add the WHEN XFD directives shown below, you add several columns to the database table. The fields which then 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 XFD 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: When a WHEN XFD directive condition is met, COBOL record definitions or REDEFINES records that are subordinate to other WHEN XFD directives are not used. Database columns in rows that correspond to those records 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 RDBMS. For example, you can select all rows for which a given column is NULL.

Example 4

This COBOL code:

01  col-type                         pic x.
 $xfd  when col-type = "a"
      03  def1                         pic x(2).
 $xfd  when col-type = "b"
      03  def2 redefines def1          pic 9(2).

results in this database table:

col_type	def1	def2
a	Xx	<Null>
b	<Null>	10