Access output on multi-value fields

[Mark Wright]

How can I get output on each line of an access query that refers to multi-valued fields.  For example, here is a sample output from a query I am doing:

  CERT   FAC CODE   TANK .....  TANK CAP. Feet Inches.  INCREMENT
  DATE              CODE                                 VOLUME

10/01/13 246176         065371     400.00    0  0.0000   0.000000
                                            20  0.0000   0.416667
10/01/13 218918         066261     300.00    0  0.0000   0.000000
                                            15  0.0000   0.416667
10/01/13 TX-6302        083749     300.00    0  0.0000   0.000000
                                            15  0.0000   0.416667
10/01/09 218943         114380     401.05    0  0.0000   0.004950
                                             3  0.0000   0.419050
                                             5  0.0000   0.417830
                                            10  0.0000   0.417720
                                            15  0.0000   0.417490
                                            20  0.0000   0.417270

I need the cert date, fac code, tank code, & tank cap fields to appear on each line.  I know there has to be a way to do that, but I can't find it in the access section of the reference manual.

I am running on D3/linux.

Thanks!!!!

MW

[frosty]

One way is to add a MultiValued dictionary definition for each Singlevalued attribute wherein you repeat the S val N times, where N is the number of values in that record's MV set.

[Mark Wright]

That doesn't sound like it would work if the number of multi-values is different for each record, like in my example.

MW

[frosty]

It sure should. I do it all the time -- but I use uniData so I can't tell you exactly how to do it in D3.

ISTR an "ND" operator in F-corr and A-corr which would give you the number of multivalues for each record.

[Mark Wright]

OK, thanks for the reply.  Hopefully someone will point me in the right direction.

MW

On Monday, December 22, 2014 4:04:03 PM UTC-6, Mark Wright wrote:

[Anthonys Lists]

On 22/12/2014 23:03, frosty wrote:
> It sure should. I do it all the time -- but I use uniData so I can't
> tell you exactly how to do it in D3.
>
> ISTR an "ND" operator in F-corr and A-corr which would give you the
> number of multivalues for each record.

In Universe, I'd use an i-descriptor and the REUSE() function. Don't
know how you'd do that in D3, though.

Cheers,
Wol

[geneb]

On Mon, 22 Dec 2014, Mark Wright wrote:

> That doesn't sound like it would work if the number of multi-values is
> different for each record, like in my example.
>

Wouldn't by-exp (by exploding) work in this instance?

g.

--
Proud owner of F-15C 80-0007
http://www.f15sim.com - The only one of its kind.
http://www.diy-cockpits.org/coll - Go Collimated or Go Home.
Some people collect things for a hobby. Geeks collect hobbies.

ScarletDME - The red hot Data Management Environment
A Multi-Value database for the masses, not the classes.
http://scarlet.deltasoft.com - Get it _today_!

[Tony Gravagno]

The traditional way to do this is with dict items, as others have suggested. I could have sworn that there was some simple connective/modifier that offered this functionality but I'm probably thinking of some MV platform other than D3.

These days in D3 you can try the OCONVS connective (familiar to those in other platforms) which allows you to put a conversion or correlative in-line in the query. This eliminates the need to create special dict items just for one report type. I don't have a clear idea of how exactly this would work in this scenario but if I was working on a project I'd run some thoughts down this avenue.

You can also use the SORTT functionality (described many times in forums over the years) to create tab-delimited output, then post-process that with some code. No, it's not elegant but it might be faster to do this than to mess with funky F-codes. The result can then be imported into Excel, MS Access, SQLite, SQLServer Express, or any other DB for attractive reporting. Personally I'd jump to this as the easy solution.  YMMV

HTH

T

[Mark Wright]

Tony,

That's probably what I will do.  I have tried the by-exp solution, as someone else suggested, & couldn't get it to do what I wanted.

Thanks for the suggestions.

MW

On Monday, December 22, 2014 4:04:03 PM UTC-6, Mark Wright wrote:

[Wols Lists]

On 23/12/14 01:35, Mark Wright wrote:
> Tony,
>
> That's probably what I will do. I have tried the by-exp solution, as
> someone else suggested, & couldn't get it to do what I wanted.

Does D3 have EVAL? Tony suggested OCONVS but I can't quite see how to
use that (I'm not familiar with it :-)

Instead of TANK_CODE or whatever it is, try EVAL "REUSE(TANK_CODE)".
That should work fine on UV, it might give you a "bingo" moment on D3.

Cheers,
Wol

> --
> You received this message because you are subscribed to
> the "Pick and MultiValue Databases" group.
> To post, email to: mvd...@googlegroups.com
> To unsubscribe, email to: mvdbms+un...@googlegroups.com
> For more options, visit http://groups.google.com/group/mvdbms

[JJCSR]

Mark:

 

I am running on REALITY, but it is a D3-compatible version, so this might work for you.    I can use subroutines, the same as I did when I was on D3, to produce the results you are seeking.  In the subroutine, address your attributes in "ACCESS()" fashion.   In a POS transaction file, I have single-value date in attribute 2, and I want it to appear on every line that the "SKU" (attribute 18) appears.   Here is the code:

subroutine vmdate(dte)
itemin=access(3)
dte=itemin<2>
return

 

Here is the dict item for "vmdate"

 

001 A
002 18
003 <<DATE>>
004
005
006
007 D2/
008 CALL VMDATE
009 R
010 8

 

"DATE" is in attribute 2 of the transaction file, but I have used "18" in att 2 of the dict item, so as to have "vmdate" be called for every value in 18, where the multi-valued SKU is located.  Thus, my output looks like this:

 TRANS.  <<DATE>> SKU CODE
  DATE

11/19/14     11/19/14             822
08/30/14     08/30/14   92660489
                   08/30/14   92661081
                   08/30/14   92660968
                   08/30/14   53636676
                   08/30/14   94668886

 

I hope this makes sense.

 

Jim Cronin

Kittery Trading Post

 

 

[fwinans]

The   by-exp  connective is nice, but will not do what you want, I think.

At our shop we typically write a program to copy the file {or selected
items of the file} into a temporary work file, creating a new item for
each relevant multivalue found in the original file item.  Each multivalue
or multi-attribute set of values causes another workfile item to be
written out by this basic program.   Now we have a workfile with simple
enough data structure to use the plain  SORT command on.

This is a bit tedious, writing a basic program for each particular file
you want to "custom  by-exp" into an associated temporary work file,
but perhaps you could eventually make a basic program that accepts
an input data file name as a parameter and then looks for a 'helper'
item in the data file dict that gives all the other settings needed to do
that one file with this adaptable 'copy to workfile' program.
Or, write your own version of the existing   REFORMAT  command
that has enough features to suit your purpose, so in a proc or macro
you can get the needed workfile done with that  super reformat   program
that gets all its args from its command line.
Whatever basic program you end up using to do the copy to workfile
could also be crafted to leave some trace behind for the parent
invoking proc or macro to more gracefully do a subsequent SORT or
LIST on the  workfile -- maybe just setting a shell variable to the work file
name, or maybe computing a full tcl command and stashing it in a
temp item in md that is named  Proc_helper@pib   for example, so the
parent proc or macro kicks off the basic program then invokes
Proc_helper@pib next.

By the way,  in the D3   REFerence manual web page, in the
"contents" view,  you will find the REFORMAT entry in the
Access Query Language  section,  not in the
Terminal Control Language    section.

On Monday, December 22, 2014 4:04:03 PM UTC-6, Mark Wright wrote:

[Scott Ballinger]

Mark,

One feature of the D3 call correlative is that it repeats once for each multi-value. You can use that to your advantage in this case: create a basic subroutine and dict item for each of the fields you want to "repeat,"  e.g. the cert date, fac code, tank code, & tank cap fields. Each basic program would look like this:

sub(ans)

record = access(3)

ans = record<n>

return

Where n is the attribute number of the field you want to display (cert date, fac code, tank code, & tank cap).

The in attribute 2 of  the dict item for each of these subroutines you reference one of the mv fields: feet, inches, increment volume. The result is that the subroutine is fired off once for each value.

For example, if  the mv field "feet" is attribute 99, then the dict item for 'cert.date.mv' might look like this:

1 S

2 99

3 cert]date

4

5

6

7 d2/

8 call cert.date.mv

9 r

10 8

It's a little cumbersome, but it should do the trick.

/Scott Ballinger

Pareto Corpration

Edmonds WA USA

206 713 6006

[CDMI - Steve T]

Mark - I would write the output in BASIC. Works on ANY flavor of multivalue every time.

normally looks better, easier to maintain because the 'BASIC' program allows for documenting, etc.

and, maybe its just me, but I can normally write a simple output program like this, way quicker than I can monkey around with the DICT items.

just my 2 cents.

"Wishing you a joyous Holiday Season and a smashing New Year!"

 

Steve Trimble (501) 615-8674

Computerized Data Mgmt Inc (CDMI)

---

From: Mark Wright <wrigh...@gmail.com>
To: mvd...@googlegroups.com
Sent: Monday, December 22, 2014 4:04 PM
Subject: [mvdbms] Access output on multi-value fields

[Tony Gravagno]

Frank, REFORMAT is an "access-class" command. It conforms to the same rules as LIST, SORT-LABEL, SELECT, etc.. That's why it's under the AQL section. The TCL section documents programs and macros.

Wol, the Eval connective, Fmt, and OconvS has been available in D3 for a couple years, since v9.1 I think. This is the kind of great functionality that's in D3, generally not used, but people will leave the platform and then brag about having it all in their new product. Go figure.

T

[fwinans]

Erm, one thing to watch out for is that
it repeats only for the   NON-EMPTY  values on that attribute; 
it doesn't bother to   CALL  the subroutine if the current value
is empty string.  For this purpose a space character is still
considered to be a non-empty value,  so whle an attribute
like  Tom]]]Dick]Harry    would do only 3 CALLs,
        Tom] ] ]Dick]Harry would  do all 5 CALLs.

[Stefano Maran]

Suppose to have the following items in testfile

    aa
001 ONE
002 TWO
003 1]2]3

    bb
001 One
002 Two
003 001]002]003

... and the following dicts
    1
001 A
002 3
003
004
005
006
007 F1R
008
009 L
010 10

   2
001 A
002 3
003
004
005
006
007 F2R
008
009 L
010 10

    3
001 A
002 3
003
004
005
006
007
008
009 L
010 10


list testfile 1 2 3


Page   1     test                                                                                           01:03:15 24 Dec 2014

testfile............ 1......... 2......... 3.........

bb                   One        Two        001
                     One        Two        002
                     One        Two        003
aa                   ONE        TWO        1
                     ONE        TWO        2
                     ONE        TWO        3

[405] 2 items listed out of 2 items.


HTH
Stefano

P.S. D3/Linux 9.0  (from dicts used in R83,,,,)

[Geoff Goodchild]

Mark,

You can use the repeat function in the A correlative to do this.  You need to concatenate the single value att, with 'R' for repeat, to the main mv att and suppress it by specifying [1,0] .
E.g. assuming Cert Date is att 12 and Feet is att 20, then a new dict defn for cert date
1  A
2  0
3  Cert Date
4
5
6
7  D2/
8  A12R:(20[1,0])
9  R
10  9

will give you what you want.  A bit contorted but.

Geoff

[Chuck Stevenson]

I don't have much to add that others haven't already said.

I notice that the dictionary examples in this thread leave attribute 4 blank.

Does that matter?

That is where multi-valued associations are defined.  

There's a "C;..." syntax for the controlling attribute of the association,

& a "D;..." syntax for all the dependent attributes/correlatives.

Does BY-EXP exploit that knowledge? 

U2 defines associations differently. UD relies on it, UV to lesser degree, but it is important.  Don't know about D3 & Reality.

"ND" is cool.  Does it report the number of values in the controlling attribute or the attribute referenced by the correlative?

I did not know that D3 had OCONVS, etc, for handling multivalued data.  Is that only from inside basic or from a correlative or to be used in a (B-?)correlative called by a subroutine?  

In D3 can you feed the correlative-called subroutine the entire record, like @RECORD in U2?  If so, you can have the best of both worlds:  write a subroutine to do the logic in basic,  which is probably more maintainable than correlatives.  But let that one subroutine be called by a set of correlatives where it passes back the desired datum from its calculated set.

Then you have the power of LIST & SELECT to do the higher level stuff.

One of the differences between Pick & Prime derivatives has been that PI fed the I-descriptor the entire multi[sub]valued field, delimiters & all, so you can/must deal with mv logic within the I-descriptor explicitly; whereas Pick fed the correlative one value at a time.  There are +/-'s to both, a 'feature' & a shortcoming, depending on what you want to accomplish.

Mark:  I, for one, would like to know what you solution is, once you solve the puzzle.  

Mostly taking, a pittance of giving, Sorry.  Not much in the spirit of Christmas, but Merry Christmas to all anyway.

Chuck Stevenson

[Chuck Stevenson]

I think Scott Ballinger already answered my question about @RECORD:

sub(ans)

record = access(3)

ans = record<n>

return

 

I take it access(3) is equivalent to U2's @record.

&, if I read him rightly, I think his approach is approximately the same as what I was trying to describe:

[Tony Gravagno]

The EVAL modifier was added to D3v9.1. This has long been a standard feature in some other MV platforms.

For those who haven't used it:

list inventory eval "(((PRICE - COST)/COST) * 100)"

That eliminates the need to create a dict item for a one-off query or an infrequently used macro.

The OCONVS function (and related TRANS function) Only applies to Eval on the command line. This is Not a BASIC function.

The FMT modifier was also added for command-line formatting:

list inventory eval "oconvs(PRICE - COST,'mr2')" fmt "'Margin'R15"

For more fun with dynamic command-line queries in D3 : http://bit.ly/d3shell2

T

[Chuck Stevenson]

That implies some sort of I-descriptor-like compiler.

But can you store the EVAL in the dict, as, oh i dunno, maybe an I- or V-type?

UniVerse insists on write permissions to the dictionary for 

EVAL to work.  How about D3?   UD?  It's both annoying & a security feature.

For Pickies who are used to correlatives, would you use the CONV keyword & stack the correlative & conversion together,  or would youdo  the correlative as an EVAL "OCONV( fld, [correlative])"

In your example, can PRICE &/or COST be multivualued?

I see OCONVS, but I don't see the full set of IFS(), EQS(), NOTS(), FIELDS(), etc. mv-handling subroutines.

The D3 documentation on Rocket's web-site was easy to find & easy to use.  That's a first for the Rocket web-site.

Happy New Year,

Chuck

On Saturday, December 27, 2014 1:13:22 AM UTC+3, Tony Gravagno wrote:

The EVAL modifier was added to D3v9.1. This has long been a standard feature in some other MV platforms.

For those who haven't used it:

list inventory eval "(((PRICE - COST)/COST) * 100)"

Chuck Stevenson

[Wols Lists]

On 30/12/14 10:43, Chuck Stevenson wrote:
> That implies some sort of I-descriptor-like compiler.
> But can you store the EVAL in the dict, as, oh i dunno, maybe an I- or
> V-type?

Yup. The whole point of EVAL is that you can create a throw-away
i-descriptor. If you're going to use it often you should use an
i-descriptor (and that's why you need wrote access, it basically saves
it as an i-descriptor, compiles and runs it, and throws it away).

Personally, I've used so many dictionaries that are chock-full of
duplicate d-types, i-types, whatever, that I'm all in favour of using
EVAL, FMT etc to modify output on the fly rather than cluttering the DICT.

Cheers,
Wol

[Tony Gravagno]

I don't know the internals but I can describe what Eval does. It essentially allows you to put an A-Correlative (a8) in the command line. The FMT facilitates dynamic a7 Conversion, a9 justification, and a3 column header. The OCONVS is the only function of its type, no other BASIC functions at the command-line. It's purpose is to allow more a7/Conversion refinement than we can get out of FMT. Similarly, the TRANS function (no BASIC equivalent) facilitates a T-translate which would get sloppy by itself in Eval. I dunno if someone at TigerLogic wasn't thinking about this or if they were looking for cross-platform compatibility somewhere, but we should be able to do T-translates with OCONVS, so it seems to me that TRANS is redundant.

For more dynamic functionality, back in 2008 and 2010 I wrote a couple blogs that describe how to do very complex queries from D3 TCL, incuding SQL-like nested queries and what I call "contextual" queries which change what they're doing depending on the user's current context. As one example, in one command you can get a list of companies that sell red widgets by nesting a query for red products within a query to sort companies. Or rather than having separate queries for A/R-HIST-2013 and A/R-HIST-2014, you can have one query for A/R-HIST which changes based on which year the user is currently researching. And you can do all of this without creating extra dict item.

I bring all that up to answer the question about storing the Eval. Well, as Wol said, you if you're storing it in the dict then you have a dict item, so you don't need a dynamic Eval. But after going through my blogs you will know how you can save an Eval or Fmt and apply them to different files, which is better than saving a dict item in every file or playing with the Using modifier.

As to documentation at Rocket, for D3 it's really just a rebranded version of the same docs that have been available for D3 for the last 6-10 years that I can remember. You can download it for local reference, or if you use the online doc then you'll get the latest updates as they tend to make fixes and enhancements live/online these days. For U2 I've always used downloaded PDFs.

D3 has capabilities that hardly anyone ever uses, but wow, people are happy with the "new" functionality they get when they migrate somewhere else. Always amazing to see that scenario play out.

T

[Anthonys Lists]

On 31/12/2014 18:10, Tony Gravagno wrote:
> I don't know the internals but I can describe what Eval does. It
> essentially allows you to put an A-Correlative (a8) in the command
> line. The FMT facilitates dynamic a7 Conversion, a9 justification, and
> a3 column header. The OCONVS is the only function of its type, no
> other BASIC functions at the command-line. It's purpose is to allow
> more a7/Conversion refinement than we can get out of FMT. Similarly,
> the TRANS function (no BASIC equivalent) facilitates a T-translate
> which would get sloppy by itself in Eval. I dunno if someone at
> TigerLogic wasn't thinking about this or if they were looking for
> cross-platform compatibility somewhere, but we should be able to do
> T-translates with OCONVS, so it seems to me that TRANS is redundant.

To the best of my knowledge, TRANS is a Pr1me-ism, and the Pr1me version
was a bugger to use. One of the very few things in INFORMATION that -
imho - wasn't "Pick done better". In all the other versions I know of,
TRANS *always* does a "lower", and always requires a "raise" in the
i-desc to get the same data in the receiving file. PI sometimes did a
lower, and sometimes didn't, and I could never make head or tail of it.
That knowledge has proved useful on a couple of occasions, when stuff
that was working fine in UV suddenly broke, and I realised that a
Prime-style trans had been recompiled into a UV style one ...

Digression aside, I believe TRANS predates OCONVS, certainly as far as
INFORMATION was concerned.

Cheers,
Wol