Re: [mvdbms] Digest for mvdbms@googlegroups.com - 13 updates in 1 topic

[Philip Klunk]

If you have access to ViaDuct, its copy to clipboard and download functions have a "Duplicate fields" feature that will do exactly what you need.

Phil Klunk

On Tue, Dec 23, 2014 at 1:32 PM, <mvd...@googlegroups.com> wrote:

mvd...@googlegroups.com

Google Groups

Topic digest
View all topics

• Access output on multi-value fields - 13 Updates

Access output on multi-value fields

Mark Wright <wrigh...@gmail.com>: Dec 22 02:04PM -0800 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 <johntr...@gmail.com>: Dec 22 02:53PM -0800 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.   On Monday, December 22, 2014 3:04:03 PM UTC-7, Mark Wright wrote:

Mark Wright <wrigh...@gmail.com>: Dec 22 02:56PM -0800 That doesn't sound like it would work if the number of multi-values is different for each record, like in my example.   MW   On Monday, December 22, 2014 4:04:03 PM UTC-6, Mark Wright wrote:

frosty <johntr...@gmail.com>: Dec 22 03:03PM -0800 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.     On Monday, December 22, 2014 3:56:48 PM UTC-7, Mark Wright wrote:

Mark Wright <wrigh...@gmail.com>: Dec 22 03:46PM -0800 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 <antl...@youngman.org.uk>: Dec 22 11:46PM On 22/12/2014 23:03, frosty wrote: > 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 <ge...@deltasoft.com>: Dec 22 04:01PM -0800

"Tony Gravagno" <bacj8...@snkmail.com>: Dec 23 12:52AM 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 HTHT

Mark Wright <wrigh...@gmail.com>: Dec 22 05:35PM -0800 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 <antl...@youngman.org.uk>: Dec 23 10:47AM 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

JJCSR <jcro...@gmail.com>: Dec 23 07:09AM -0800 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 <fwi...@sbcglobal.net>: Dec 23 07:16AM -0800 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 <scott.b...@gmail.com>: Dec 23 09:35AM -0800 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

Back to top

You received this digest because you're subscribed to updates for this group. You can change your settings on the group membership page. To unsubscribe from this group and stop receiving emails from it send an email to mvdbms+un...@googlegroups.com.

--

Phil Klunk
pkl...@gmail.com

[Tony Gravagno]

MOD Note: Please only quote what's necessary. If you're find you're having a problem deleting HTML that comes from Google, please convert your posting to plain text and you'll be able to select/delete anything you wish.
Thanks.