Use QSELECT to Explode (Normalize) MultiValue Data

This tech tip is about a nice use of “QSELECT”: a verb used to explode multi-value fields into a list of individual values. The technique illustrated works with any UniVerse or UniData application where keys for one file are stored in another file (DataFlo is one such application). This method provides a much faster (and often easier) approach to getting certain subsets of records than having to perform end-to-end searches through an entire data file. The best way to illustrate this usage is probably by example.

Let’s suppose you want a listing of all of the open line items for sales orders dated in July, 2002. In DataFlo, the main file for sales orders is called the "SO" file. The first thing we would do is find the open sales orders:

SELECT SO WITH DATE >= "07-01-02" AND <= "07-31-02" AND WITH STATUS= "N""I""O"

The line items for sales orders are stored in the "SOLI" file. Attribute 5 of the SO file contains a multi-valued list of keys to the line items stored in the SOLI file for each sales order, so QSELECT can be used, while the select list is active from the initial select statement, to pick up the SOLI record identifiers from specific Sales Orders.

Thus, we use QSELECT to create a list of SOLI IDs based on attribute 5:

QSELECT SO (5)

We now have a list with the SOLI record IDs for open July sales orders. We can use this list to then print out our report:

SORT SOLI WITH STATUS = "N""I""O" AND WITH OPN_QTY > "0" BY PART_NO BY-EXP DATE BREAK-ON PART_NO DATE TOTAL OPN_QTY HEADING "'T'  Open Sales Order Line Items for July Orders  ' L'"

This technique lets you take advantage of the full power of the first file when trying to select records in the second.

UniData and UniVerse are products, copyrights and trademarks of Rocket Software