KnowledgeBase 00091: Value Counting in Query Reports

Last updated: 17 Feb 2017 Applies to: All versions

Search   Top level index       Full Index Search Tips Previous article     Next article

This article was originally published as a Tip of the Week.

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

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

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

   ORDER    LINE  ITEM    QTY  SERIAL 
   12001       1   147     3   765394 
                               765397 
                               765406 
               2   218     1   177643 
   12002       1   304     2   214458 
                               215374 
   12003       1   718     3   118654 
                               118757 
                               118774 
               2   126     1   438740 

Setting the repeat argument to true changes the report to be

   ORDER    LINE  ITEM    QTY  SERIAL 
   12001       1   147     3   765394 
               1               765397 
               1               765406 
               2   218     1   177643 
   12002       1   304     2   214458 
               1               215374 
   12003       1   718     3   118654 
               1               118757 
               1               118774 
               2   126     1   438740 

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

   LIST SALES LINE ITEM QTY SN SERIAL 

would produce

   ORDER    LINE  ITEM    QTY  SN  SERIAL 
   12001       1   147     3    1  765394 
                                2  765397 
                                3  765406 
               2   218     1    1  177643 
   12002       1   304     2    1  214458 
                                2  215374 
   12003       1   718     3    1  118654 
                                2  118757 
                                3  118774 
               2   126     1    1  438740 

Related Articles

None.

Please tell us if this article was helpful

Very     Slightly     Not at all

Comments

Email (optional)