Author Topic: Using DUP to avoid being Duped  (Read 7560 times)

Tom Pellitieri

  • Rock Star
  • *****
  • Posts: 224
  • Tom Pellitieri - Toledo, Ohio
Using DUP to avoid being Duped
« on: August 08, 2007, 03:15:51 pm »
One of the frustrating things that I've encountered is being able to index another file when only PART of the key is available in a Multi-Valued field.  SB+ provides a solution in the expression language:  the DUP function.  However, the parameters don't always work as I expect them to, so here's what I do to make it work.  (If someone can simplify this, I'd appreciate it!)

The DUP function is defined as DUP(exp1,exp2), and is supposed to return exp1 repeated as many times as there are values in exp2.  However, I have found that if you use anything other than an Attribute Number (e.g., <5>) for exp2, this doesn't work!

I'm sure many will agree that using the attribute number explicitly is not the best way to document what you're doing, nor is it easy to maintain changes.  Fortunately, the POS function will return the attribute number given the field name.  My solution is to use DUP(exp1,<POS(mv.field.name)>) when I need to do this.

There's still one more problem:  even though I've copied the "static" portion for each value in mv.field.name, if I just concantenate mv.field.name, I only get the first value included!  Again, I have to use <POS(mv.field.name)> to get all of my values appended properly.

For example, Prelude has a Product/Serial Number history file (PROD.SN) that uses "company!product!serial.number" as a record key.  The Order History Line detail file has the Company as part of the record key and a single value for Product.  Since there is a Stock Serial Number for each unit shipped, that attribute is Multi-Valued.

In order to get the keys to PROD.SN from a record, I've defined a derived Multi-Valued field (0.M) using the formula

(DUP(CO.NUM:"!":PROD.NUM:"!",<POS(STK.SER)>):<POS(STK.SER)>)

This correctly duplicates the static portion for each serial number, and appends each serial number.

--Tom Pellitieri

precisonline

  • President
  • Administrator
  • Rock Star
  • *****
  • Posts: 1609
    • Precision Solutions
Re: Using DUP to avoid being Duped
« Reply #1 on: August 08, 2007, 07:00:47 pm »
The reason why DUP sometimes fails is not actually a failure with DUP but rather a feature of SB+ that whenever you refer to any field by name (like STK.SER) and @CNT is anything but 0, STK.SER resolves to:

@RECORD<n,@CNT>

...where n is the field position for the STK.SER field.  If you use /EEGC to look at the source for DUP you'll see that it DCOUNTs the second parameter and then uses STR(..) to repeat the first parameter that many times (prepended by a value mark).  So if @CNT is 2 and you use:

STR("A",STK.SER)

..and the second multivalue of STK.SER is null, the result will be a string of A's repeated DCOUNT(RECORD<n,2>,@VM) times (zero), producing null as the result.

Incidentally, as far as I have ever seen, this:

(DUP(CO.NUM:"!":PROD.NUM:"!",<POS(STK.SER)>):<POS(STK.SER)>)

...is the best syntax available to achieve what you want.
-Kevin
Accidents "happen"; success, however, is planned and executed.