KnowledgeBase 00091: Value Counting in Query Reports

Introduction

Pick style multivalue environments include the NV and NS tokens that can be
used in correlative expressions to identify the value position or subvalue
position of the data item being processed. The Information style environments
such as QM do not evaluate the expression separately for each value/subvalue
but instead make use of the ability of the arithmetic operators to work on
multvalued data, performing the entire expression evaluation in a single step.
As a result of this, the NV and NS tokens cannot work as they do in Pick
style systems.

Consider as an example a sales system that includes order records that contain
a list of item codes. A simple query

LIST SALES ITEM QTY

might show

ORDER ITEM QTY
12001 147 3
218 1
12002 304 2
12003 718 3
126 1

The NV() Function

We might want to insert an additional column with the line number from within
each order

What is needed is a way to generate a multivalued item where the elements
contain their value position but with the same number of values present as
there are in the data item being processed. Because the record may contain
many multivalued fields with differing numbers of values, it is necessary to
relate this generated list of value positions to a specific field. The data
for the LINE column in the above query can be generated using the NV()
function.

NV(ITEM)

where it will return a list of value numbers corresponding to the values
present in the ITEM field. This expression could be saved in the dictionary
as an I-type item or it could be evaluated on the command line using the
query processor EVAL keyword.

LIST SALES EVAL "NV(ITEM)" COL.HDG "LINE" ITEM QTY

Handling Subvalues

We can extend the above discussion to cover fields that are divided into
values and subvalues. Now there are two ways in which we might want this to
work.

If we want the NV() function to repeat the value position for each subvalue,
we can add use

NV(ITEM, @TRUE)

where the optional second argument indicates whether the value position should
be repeated for each subvalue.

Perhaps we need to store the serial number of each item that we sell. Without
the second argument to the NV() function or specifying this as false, the
report appears as

The NS() Function

The NS() function works in the same way as the first form of NV() but returns
a list of subvalue positions in a specified data item. If we have dictionary
items LINE that evaluates to NV(ITEM) and SN that evaluates to NS(SERIAL),
a query