How to use BY-EXP to Explode Multi-Values

The Rocket UniData/UniVerse (U2) databases are also now referred to as "extended relational databases".

The U2 relational database (RDBMS) environments used by the DataFlo ERP application are often referred to as “MultiValue” (MV) databases. One reason they are referred to this way is because the database has an inherent capability to store “like” or “related” data in fields (attributes) which are related to each other. In these instances, multiple values of the same “type” of data are stored in one field (attribute). These “related” fields are often referred to as “datasets”.

A good example of storing related sets of data in a record in the DataFlo ERP package is the BOM (Bill of Material) file. The BY-EXP modifier is a very useful tool for “exploding” these multi-value datasets and making them behave as if each sub-value set of data is an individual record. This tech tip describes the use of the BY-EXP modifier using the BOM (Bill of Material) data file as an example.

In the UniQuery ad-hoc report example below, three BOM records are listed. Note that although we are using the SORT verb in the example and we attempt to sort the records by PART_NO, the report simply lists each BOM record with the line items that make up each record.

SORT BOM BY PART_NO BOM_NO LI_NO PART_NO LI_PART_DESC QTY START_DATE END_DATE  ID.SUP

11:25:46am  14 Dec 2004  PAGE    1
..BOM Number... LI  ..Part Number.. .......Description....... Quantity. Start Use End Use.
                                                                          Date      Date

950-01            1 102-02          PRINTED CIRCUIT BD           3.0000
                  2 612-01          MEMORY BOARD/SLOTS           1.0000
                  3 612-00          MEMORY CHIP 256 MB           1.0000
                  4 612-03          SCSI PORT                    1.0000
950-00            1 950-01          MOTHERBOARD 2MB RAM          1.0000
                  2 950-09          PEDESTAL UNIT FOR 400 CPS    1.0000
                  3 950-12          GRAPHICS BOARD FOR 150LPM    1.0000
950-06            1 950-05          4MB ECC RAM MEMORY UNIT     -1.0000
                  2 951-06          16MB ECC RAM MEMORY UNIT     1.0000

3 records listed.

Note what happens when we replace the BY modifier with the BY-EXP modifier in the UniQuery statement below:

SORT BOM BY-EXP PART_NO BOM_NO LI_NO PART_NO LI_PART_DESC QTY START_DATE END_DATE  ID.SUP 

11:46:11am  14 Dec 2004  PAGE    1
..BOM Number... LI  ..Part Number.. .......Description....... Quantity. Start Use End Use.
                                                                          Date      Date

950-01            1 102-02          PRINTED CIRCUIT BD           3.0000
950-01            3 612-00          MEMORY CHIP 256 MB           1.0000
950-01            2 612-01          MEMORY BOARD/SLOTS           1.0000
950-01            4 612-03          SCSI PORT                    1.0000
950-00            1 950-01          MOTHERBOARD 2MB RAM          1.0000
950-06            1 950-05          4MB ECC RAM MEMORY UNIT     -1.0000
950-00            2 950-09          PEDESTAL UNIT FOR 400 CPS    1.0000
950-00            3 950-12          GRAPHICS BOARD FOR 150LPM    1.0000
950-06            2 951-06          16MB ECC RAM MEMORY UNIT     1.0000

9 records listed.	

As you can see from this example, the BY-EXP modifier “explodes” each BOM record dataset and makes it behave as if each dataset were an individual record. Notice that in this second UniQuery report that the records are now listed in PART_NO sequence. In this example, each of the data fields in this report to the right of the BOM number are all related to one another. Each line in the bill of material makes up the dataset. A special dictionary descriptor in the BOM dictionary declares this relationship between the various fields (attributes). In this instance the descriptor that defines the relationship for these fields is called LI_INFO1 and has the following structure:

>ED DICT BOM LI_INFO1
2 lines long.

----:
0001: PH
----:
0002: F11 F12 F13 F14 F15 F16 F17 F18 F19 F22 F23 F25 F26 F27 F28 F31 LI_PT_DESC
 COMP_OPERATION OPER COMP_YIELD QTY BEFD LI_PART_DESC END_DATE REF_DES1
 COMP_QTY PART_NO COMP_PART PN COMP_TYPE YIELD ECO_NO LI_NO COMP_DESC 
 QUANTITY TYPE START_USE ECO_DATE REF_DES LI_DESC START_DATE END_USE ENDTEST
 LONG_COMP_TYPE XPL ITEM EEFD

Bottom at line 2.

Most of these relationships are pre-defined if you are using the DataFlo ERP application. If you are using the BY-EXP for attributes that do not already have this relationship defined, you will need to create a similar descriptor as shown above to define the multi-value relationship. This presumes the data relationship already exists in the records and you must maintain these relationships either by data entry or via program logic.

Note: If you use the wIntegrate Query Builder utility, you can use the BY-EXP modifier in place of the BY modifier even though wIntegrate does not list the BY-EXP as a modifier.

DataFlo is a product, copyright, and trademark of Epicor Corporation