|
|
|
* This file is intended to be used as a tutorial
* for c-treeRTG COBOL Edition developers to learn
* about the multiple ways that a file descriptor
* is mapped into a SQL table within FairCom DB SQL.
* All copyrights belong to FairCom, Corp.
* **********************************************************
* The main file of this tutorial is CARDFILE, a file that
* contains records of a reward membership card for customers.
* **********************************************************
FD CARDFILE.
01 CARDRECORD-MASTER.
05 CARDRECORD-KEY.
10 CARD-RECORD-NUMBER PIC 9(10).
10 CARD REDEFINES CARD-RECORD-NUMBER.
15 CARD-1 PIC 9(6).
15 CARD-2 PIC 9(4).
* **********************************************************
* -----------------> TUTORIAL HINT <-----------------------
*
* By default xddgen expands the innermost fields.
* The "USE GROUP" directive is used here to force xddgen,
* when building the SQL table, to consider the entire
* CARD-TITLE-NUMBER group as one field instead of expanding
* its subfields. In this case we want to expose it and not
* its children.
*
* **********************************************************
*>>XDD USE GROUP
10 CARD-TITLE-NUMBER.
15 CARD-TITLE-NUMBER-1 PIC 9(4).
15 CARD-TITLE-NUMBER-2 PIC 9(6).
10 CARD-TITLE-NUMBER-X REDEFINES CARD-TITLE-NUMBER
PIC 9(10).
10 CUSTOMER-NUMBER PIC 9(4).
10 ELITE-MEMBER-TYPE PIC X.
88 CARD-GOLD VALUE "G".
88 CARD-PLATINUM VALUE "P".
88 CARD-SILVER VALUE "S".
***********************************************************
* CARD IDENTIFICATION DEFINITION
***********************************************************
***********************************************************
* -----------------> TUTORIAL HINT <-----------------------
*
* This is an example of the usage of multi-record with SQL.
* You can notice that CARD-TABLE is redefined later in this
* file by CARD-TABLE-PLATINUM which includes additional fields.
* This means that this table in COBOL has two different types
* of records, with different size. c-treeRTG can handle this
* properly, as long as you indicate what are the rules that
* the SQL server needs to use to decide which record belongs
* to which table. To do this, you use the following XDD directive:
* *>>XDD WHEN ... TABLENAME=...
* This will instruct c-treeRTG to create two separate SQL tables
* representing each separate set of records, depending on the
* condition that has been established for values within each
* record.
* Notice that physically there is still a single COBOL file
* which allows you to run your COBOL programs with no modification.
* c-treeRTG handles this dynamically, building SQL tables as
* if they were actually views, and updating the indexes in
* accordance to the file definition.
* In this example, we are forcing c-treeRTG to create two tables
* one for regular members ad one for Platinum members.
***********************************************************
*>>XDD WHEN ELITE-MEMBER-TYPE != "P" TABLENAME="REGULARMEMBERS"
05 CARD-TABLE.
***********************************************************
* -----------------> TUTORIAL HINT <-----------------------
*
* There are at maximum three copies of each membership card
* assigned to different family member, of which the first one
* is only assigned while the other two are optionally assigned.
* We needed to build an index on CARD-FAMILY-NUMBER of the
* first copy of the card. Therefore instead of coding a
* OCCURS 3, we explicitly add fields for the first card copy and
* coded a OCCURS 2 for the rest.
***********************************************************
*>>XDD USE GROUP
10 CARD-FAMILY-NUMBER-1.
15 CARD-LABEL-NUMBER-1 PIC 9(4).
15 CARD-MAIN-NUMBER-1 PIC 9(8).
15 CARD-NUMBER-CRC-1 PIC 9(2).
*>>XDD DATE = YYMMDD
10 EMISSION-DATE-1 PIC 9(6) COMP-6.
***********************************************************
* -----------------> TUTORIAL HINT <-----------------------
* The example below shows how to combine multiple XDD directives
* into a single line. In this example, we combined USE GROUP
* to force SQL to use the name of the group as the SQL field
* and a specific format to display this field as a date
* as YMD. There are multiple other formats available, please
* refer to the documentation for other options.
***********************************************************
*>>XDD USE GROUP
*>>XDD DATE=YYMMDD
10 VALID-UNTIL-DATE-1 PIC 9(6) COMP-6.
***********************************************************
* -----------------> TUTORIAL HINT <-----------------------
* Here is the OCCURS 2 we talked about earlier.
* OCCURS are handled automatically by c-treeRTG when converting
* this table into SQL. xddgen automatically expands every
* occurr into multiple fields, using numbered extensions such
* as "_1", "_2" and so on.
* Therefore the fields would be named as follows
* CARD_FAMILY_NUMBER_1
* EMISSION_DATE_1
* VALID_UNTIL_DATE_1
* CARD_FAMILY_NUMBER_2
* EMISSION_DATE_2
* VALID_UNTIL_DATE_2
* The first three fields cause a field name conflict, with the
* some fields previously defined in the structure (the three fields we
* specified outside the occurs at lines 57, 63, 76).
* This field name conflict does not allow the table to be "sqlized"
* since field names must be unique.
* The solution to this is to use the *>>XDD NAME directive to assign
* a different name for SQL to the conflicting fields.
* the result of the directive below is the following fields:
* EMISSION_DATE_1
* VALID_UNTIL_DATE_1
* CARD_FAMILY_NUMB_1
* EMISSION_1
* VALID_UNTIL_1
* CARD_FAMILY_NUMB_2
* EMISSION_2
* VALID_UNTIL_2
***********************************************************
10 GROUP-TITLE-INFO OCCURS 2 TIMES.
*>>XDD USE GROUP
*>>XDD NAME=CARD_FAMILY_NUMB
15 CARD-FAMILY-NUMBER.
20 CARD-LABEL-NUMBER PIC 9(4).
20 CARD-MAIN-NUMBER PIC 9(8).
20 CARD-NUMBER-CRC PIC 9(2).
*>>XDD USE GROUP
*>>XDD NAME=EMISSION
*>>XDD DATE = YYMMDD
15 EMISSION-DATE PIC 9(6) COMP-6.
*>>XDD USE GROUP
*>>XDD NAME=VALID_UNTIL
*>>XDD DATE=YYMMDD
15 VALID-UNTIL-DATE PIC 9(6) COMP-6.
***********************************************************
* -----------------> TUTORIAL HINT <-----------------------
* The example below shows the usage of HIDDEN as a directive
* that will instruct SQL to not display this field.
* With this directive, this field will simply not be displayed.
***********************************************************
*>>XDD HIDDEN
10 RESERVED PIC X(4).
***********************************************************
* -----------------> TUTORIAL HINT <-----------------------
* Fillers are hidden by default so there is no need to use
* directives here.
***********************************************************
10 FILLER PIC X(10).
***********************************************************
* -----------------> TUTORIAL HINT <-----------------------
*
* Here it comes the platinum member table we described above
***********************************************************
*>>XDD WHEN ELITE-MEMBER-TYPE="P" TABLENAME="PLATINUMMEMBERS"
05 CARD-TABLE-PLATINUM REDEFINES CARD-TABLE.
10 GROUP-TITLE-INFO-PLT OCCURS 3 TIMES.
15 CARD-FAMILY-NUMBER-PLT.
20 CARD-LABEL-NUMBER-PLT PIC 9(4).
20 CARD-MAIN-NUMBER-PLT PIC 9(8).
20 CARD-NUMBER-CRC-PLT PIC 9(2).
*>>XDD DATE=YYMMDD
15 EMISSION-DATE-PLT PIC 9(6) COMP-6.
*>>XDD DATE=YYMMDD
15 VALID-UNTIL-DATE-PLT PIC 9(6) COMP-6.
10 GROUP-REWARDS-POINTS.
15 TOTAL-POINTS PIC 9(8).
*>>XDD DATE=YYMMDD
15 EXPIRATION-DATE PIC 9(6) COMP-6.
*>>XDD DATE=YYMMDD
15 LAST-TRANS-DATE PIC 9(6) COMP-6.
05 CARD-GROUP-NUMBER PIC 9(4).
05 CARD-EMBOSS-FLAG PIC X(1).
*>>XDD USE GROUP
05 CARD-CUSTOMER-NAME.
10 CARD-CUSTOMER-LAST PIC X(30).
10 CARD-CUSTOMER-FIRST PIC X(20).
10 CARD-CUSTOMER-M PIC X(1).
05 CARD-ADDRESS.
10 CARD-STREET PIC X(40).
10 CARD-STREET-2 PIC X(40).
10 CARD-CITY PIC X(15).
10 CARD-STATE PIC X(2).
10 CARD-ZIP PIC 9(5) COMP-6.
05 CARD-CUSTOMER-SEX PIC X(1).
88 CARD-MEMBER-MAL VALUE "M".
88 CARD-MEMBER-FEM VALUE "F".
*>>XDD DATE=YYYYMMDD
05 CARD-CUSTOMER-BIRTH-DATE PIC 9(8) COMP-6.
