The F;x;P;S;_ trick : Getting the last value from an attribute
For many years Pick developers have relied on a trick to extract the last value element from a multi-valued attribute. I don’t have access to internal F-correlative code to explain why it really works but I can provide an explanation based on observations, along with a couple variations of the trick to explore how it behaves. I welcome corrections from someone with a clue. (Chandru M? Henry E? Mark B? Dick P? anyone still alive?)
This was prompted by the following U2 forum exchange:
From: Brian Leach
Jim Volkman wrote:
>Last value:
> 002 9
> 008 F;9;P;S;_Not sure what you’re trying to achieve here. It’s equivalent
to @RECORD<9> – SUM(@RECORD<9>)..Or am I missing something?
Yeah, that’s an old Pick trick, in that case to get the last value from attribute 9. I don’t know if it’s a bug or feature that makes it work but here’s how it plays out:
- Put the MV attribute 1]2]3 onto the top of the stack.
- Duplicate the top stack element with a Push.
Stack now looks like this:
1]2]3
1]2]3 - Sum the top stack element.
Stack now looks like this.
6
1]2]3 - Swap the top two stack elements.
And that’s where the bug/feature lies. Only the last value is swapped up. So the final stack looks like this:
3
6
And only the top stack element is returned to the output line … 3.
Variations
I originally thought that this is because the top of the stack is single-valued and the “_” operator has something wrong with the way it counts values when the value count of the stack elements is different. Then I discovered that the bug (no longer considered a feature) manifests itself with the “R” operator as well as with the “_” operator. In other words: F;x;P;S;R.
The “/” operator divides stack 2 by stack 1, and returns the quotient to stack 1. Results of division operations are rounded down. The “R” operator is the same as “/”, except the remainder of the division is returned to stack 1.
It seems that after the top of the stacked is Summed to a single value, the Remainder function behaves just like “_” and returns the last value of the second stack element. It does not return the remainder of any division operation. The “/” operator does not behave the same.
While the traditional definition of the trick is to use F;x;P;S;_ (where x is the attribute), the trick can also be accomplished using F;x;x;S;_ (hardcoding the MV list onto the stack twice). The idea here is that you’re simply putting a list of multivalues onto the top of the stack and them Summing them before swapping the stacks.
Hmmm, I wonder if you really need to Sum a list of multivalues? Nope. Try F;x;C1;S;_. All that does is put the Constant number ‘1’ on the top of the stack, Sum it (1 plus nothing is 1). But when the stack elements are swapped we still just get the last element.
Now, if you leave out the Sum, and just use F;x;C1;_, you’ll see all multivalues displayed. This confirms that the bug is in the Sum processor code, perhaps leaving some dirty bits in workspace that the “_” and “R” code trips on later.
Almost certainly, the only platform exhibiting that behavior would be D3, inherited from its predecessors Advanced Pick and R83 before that. The code is simply too specific to have coincidentally crept into other platforms – unless perhaps the same code was in Microdata but I’d guess it was re-written in Reality some years ago. So I would call this a D3-specific bug and leave it to the other DBMS vendors to determine if they want to emulate it.
Should other platforms emulate this functionality?
MV DBMS platforms that do not duplicate this functionality impose a migration impediment for (major guess here) about 30% of the Pick-based applications out there. That is, a lot of Pick people have relied on that functionality for decades, but since this isn’t documented except in tips-n-tricks notes once in a while, a lot of other people don’t know about it or have chosen not to use it. The fact is that some of the older MV implementations do support the behavior, including Ultimate, mvBase, and mvEnterprise.
But what about QM, Universe, Unidata, jBase, Reality, Caché, and OpenInsight?
If they don’t already emulate the S;_ behavior, I think they should allow it only as an option, if at all. One may legitimately want to sum the top stack element and then swap the top two entries to operate on all values of the second element. But how many people really use F correlatives like this anymore anyway? So for that (perhaps) 30% of apps migrating that make use of this trick, the code can changed without a lot of pain, so personally I’d vote to not reproduce the bugs and to ask sites migrating to fix the few places in their code where this occurs.
About the S;R variant, I can’t think of a legitimate reason to use the remainder function between two stack elements that have a different number of values. Not for practical reasons but just as a Pick professional I’d be interested to know what various platforms do with this. In this case I’d vote to ensure that it works correctly in all MV platforms, perhaps including D3 with a flag to change the behavior.
If you’re on a R83-based platform (versus Prime) and you think that at some time in the future you may migrate, I think it would be good to identify all uses of this code so that it can be converted to something platform-specific later. Search your dicts (programmatically of course) for the fairly unique strings “S;_” and “S;R” and you should find all of these. Repeat that on your code set in case someone got cute and used the trick in an OCONV.
Note from the above that because there is no defined way to get the last attribute in Pick-based platforms, Pick developers have needed to rely on this bug/trick. Other platforms have no reference platform to implement working functionality, so I’d guess that they’ve all implemented this differently, if at all. I’m thinking the solution you’ll hear is to avoid F-correlatives entirely and convert the dicts to I-descriptors or to call to BASIC. Say what you will about the benefits of newer platforms, but I have to think twice whenever someone tells me I need to re-write existing simple code into something more complex in order to reproduce simple functionality. (I guess that’s the way a lot of Pick people think about Java, .NET, etc…)
If you know for sure that this works or doesn’t work in a non-D3 system, please post a comment including the DBMS and Release, or tell us how to do this in other platforms. Thanks!