WHEN Directive with TABLENAME Clause

If you assign a table name, a VIEW will be created that contains the columns subordinate to the WHEN directive and any columns the WHEN directive depends on.

When an XFD names a condition, such as WHEN, AcuXDBC produces multiple tables from a single XFD file. One table is given the current name of the file, while the VIEWS resulting from any named conditions are given the name specified with the tablename parameter (see syntax below).

You cannot include a HIDDEN field in a WHEN directive with a TABLENAME clause, because of 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

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

Note: If a WHEN condition sets a TABLENAME, any WHEN OTHER condition specified for the same WHEN directive must also set a TABLENAME.

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 the system catalog through a program like Access, you would see a table named ar_table, and two views named ship, and backorder. If you placed $XFD READ-ONLY TABLE immediately before the

xfd  when ar-code-type = "s" tablename=ship

line, the ship view and ar_table would be read-only, but the backorder view would not. If you create the INFORMATION_SCHEMA during AcuXDBC setup, you can see these entries by executing the following SQL query:

SELECT * FROM INFORMATION_SCHEMA.VIEWS;

OTHER may be used before one record definition and may be used once at each level within each record definition.

See Creating a System Catalog and Views for more information.

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 thus are 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

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