![]() |
Powered by QM on a Rpi server |
|
KnowledgeBase 00091: Value Counting in Query ReportsThis article was originally published as a Tip of the Week. IntroductionPick 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 QTYmight show ORDER ITEM QTY 12001 147 3 218 1 12002 304 2 12003 718 3 126 1 The NV() FunctionWe 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 SubvaluesWe 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 438740Setting 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() FunctionThe 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 SERIALwould 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 ArticlesNone. |