SELECT fields into a list with some multivalues

Suppose you have a file like TEMP that has NAME, ADDRESS, and PHONE where NAME and ADDRESS are both single values but PHONE is multi value.

Like so:

TEMP..................... NAME...... ADDRESS... PHONE.....
1                         NAME1      ADDRESS1   PHONE1
2                         NAME2      ADDRESS2   PHONE2-1
                                                PHONE2-2
                                                PHONE2-3

If you

SELECT TEMP NAME ADDRESS

then you will get a list where each record supplies two items to the list, namely the NAME as one item and ADDRESS as another item:

001 NAME1
002 ADDRESS1
003 NAME2
004 ADDRESS2

However, if you

SELECT TEMP NAME ADDRESS PHONE

then you get at minimum 3 elements per item, but if PHONE happens to have mulitple values, no NAME or ADDRESS is supplied to go with each value.

001 NAME1
002 ADDRESS1
003 PHONE1
004 NAME2
005 ADDRESS2
006 PHONE2-1
007 PHONE2-2
008 PHONE2-3

How can I get blanks inserted for NAME and ADDRESS when PHONE has multiple values?

Can it be done?

Hi Tom,

To my knowledge, No.

But for me, the deeper question is "What are you trying to achieve"?

For me, a SELECT is to garner keys or foreign keys to be processed through some next step; which in the example you have makes little sense.

Again, for me, this is doing exactly what I would expect the SELECT to do which is to list the values of each dictionary item per record; in the sequence stated across the command line.

Perhaps the answer would lie in some form of exploded sort? I've never tried; but even it that works, I'm still curious as to why you want multiple fields returned in a single list?

Can you elaborate, and maybe I'll learn something?

Cheers,

Hi David,

I was afraid of that.  I'm writing a generic CSV generator that takes any AQL statement, throws away unneeded like HEADING, TOTAL, and BREAK-ON, executes the specified select statement, and sends the resulting CSV formatted output to our terminal emulator which passes it off to Excel or whatever program is associated with CSV.  I could write it all in basic, except that many of our dictionary words use CALL to derive their output and I cannot call a function that makes use of the ACCESS function from within basic.  Without blanks as place holders, there is no way to know when one record ends and the next begins that I am aware of. I was hoping there was an option or some way to present the select so the output would be more strictly ordered.

How does REFORMAT handle this situation? 

Tom

REFORMAT gives the same results UNLESS controlling/dependent relationships are properly setup for multi values. Then it makes multi value results as you would expect.  This may represent an opportunity to make some sense of it.

------------------------------
Tom Marracci
General Manager
Aircraft Spruce
corona CA US
------------------------------

Hi, Tom!

I think your terminology may be confusing us, so let me tell you what I think you may be trying to do, and where it's going wrong.

Your ultimate goal seems to be to download data from your D3 host to a local PC via capture or kermit or something like that.  Is that right?

Also, you're trying to format that data prior to download into a .csv format that Excel can import directly, and represent as it would appear on a terminal when listing the file with NAME, ADDRESS, PHONE, etc.  Right?

And your problem becomes ... just guessing here ... getting the multivalue data to appear stacked, on its own row, in Excel, much as it would appear on the terminal using The Command Line.  Right?

If so, then what you need to do is replace those value marks with CR (... or LF or both?) (I forget which.) just as you now replace your attribute marks with TAB ... AND double-quote those fields/attributes.  This causes Excel to stack those phone numbers (or whatever) inside a  single cell.

Have I understood you correctly?

------------------------------
Marcus Rhodes
marcus1@thinqware.com
------------------------------

Hi Marcus,

The problem is well before the file ever gets to Excel.  

For the sake of clarity, what I was hoping to get from the SELECT statement was the following:

001 NAME1
002 ADDRESS1
003 PHONE1
004 NAME2
005 ADDRESS2
006 PHONE2-1

007

008
009 PHONE2-2

010

011
012 PHONE2-3

Lines 7,8 and 10,11 are the blanks that I would expect for NAME and ADDRESS when the value for PHONE is 2 or higher.  Instead, the SELECT processor outputs nothing when there are multi values mixed with non multi attributes.  The output processor handles this correctly when reports are generated, but if I try to use SELECT as a data processor and not a report processor, I get garbled results when multi values are present.

I discovered through some testing that BY-EXP will correctly insert each single value as if it was part of the set so the data stays aligned which is helpful but will not always be the case for any and every report.  REFORMAT will do it correctly to a temp file but only if the controlling/dependent relationships (that's the C; and D; values on attribute 4 of the dictionary items) are defined which should be the case but, as you may well know, is not always there.  Otherwise, REFORMAT does the same as the SELECT method.

Does that make sense?

This is what I mean by terminology.  SELECT does nothing like what you describe.  SORT or LIST will.  Is that what you mean?

Also I see only attributes, not multivalues; every datum is shown as occupying it's own attribute/field.  Where are the multivalues?

Marcus,

In this example, the data would look like this:

item id: 1

001 NAME1

002 ADDRESS1

003 PHONE1

item id: 2

001 NAME2

002 ADDRESS2

003 PHONE2-1]PHONE2-2]PHONE2-3

Let's say the file is called CUSTOMER.

If you SELECT CUSTOMER NAME ADDRESS PHONE instead of a list of customers id's, you get the data for NAME, ADDRESS, and PHONE.  The system will perform any correlatives, but NOT conversions. My program has to apply conversions to each data element before it is output.

Simply put, assuming I know there are 3 fields per record then I could do something like:

001 CNT = 0
002 LOOP
003 READNEXT DATUM ELSE EXIT
004 CNT += 1
005 IF MOD(CNT,3) # 1 THEN PRINT ',':
006 PRINT DATUM:
007 IF MOD(CNT,3) = 0 THEN PRINT
008 REPEAT

But when the SELECT leaves out NAME and ADDRESS for multi valued PHONES, the data are now out of sync and this won't work.

What I'm hoping to do if insert into our application, when directed by the user, a single instruction to use my new CSV program like a filter without changing the LIST or SORT statement in anyway.  Just reinterpret it to output a CSV file instead of a report.  Simple concept but made complex by multi values in the result set not leaving any placeholders.   Without them I'm having a difficult time finding a way to keep the data in order.

Yeah ... no.  SELECT doesn't work the way you want it to.  You can select the records you want, and then print them to the screen/capture, but you can't blend the dictionary items into the SELECT command.  Well, you 'can', but you won't get what you seem to be aiming for.

Try something like this:

SELECT THE_FILE TO ITEM_LST

! Start the capture!  (Using AccuTerm?)

LOOP

   READNEXT ITEM_KEY FROM ITEM_LST THEN

      READ THE_ITEM FROM THE_FILE, ITEM_KEY THEN

         CRT CONVERT( THE_ITEM, @AM : @VM, TAB : ',' )

      END

   END ELSE

      EXIT

   END

REPEAT

! End the capture!

Suppose you have a file like TEMP that has NAME, ADDRESS, and PHONE where NAME and ADDRESS are both single values but PHONE is multi value.

Like so:

TEMP..................... NAME...... ADDRESS... PHONE.....
1                         NAME1      ADDRESS1   PHONE1
2                         NAME2      ADDRESS2   PHONE2-1
                                                PHONE2-2
                                                PHONE2-3

If you

SELECT TEMP NAME ADDRESS

then you will get a list where each record supplies two items to the list, namely the NAME as one item and ADDRESS as another item:

001 NAME1
002 ADDRESS1
003 NAME2
004 ADDRESS2

However, if you

SELECT TEMP NAME ADDRESS PHONE

then you get at minimum 3 elements per item, but if PHONE happens to have mulitple values, no NAME or ADDRESS is supplied to go with each value.

001 NAME1
002 ADDRESS1
003 PHONE1
004 NAME2
005 ADDRESS2
006 PHONE2-1
007 PHONE2-2
008 PHONE2-3

How can I get blanks inserted for NAME and ADDRESS when PHONE has multiple values?

Can it be done?

Hi Tom,

I think this should work

SELECT temp BY-EXP PHONE

SELECT TEMP BY NAME

SELECT TEMP NAME ADDRESS PHONE

The one possible draw back is that your resulting list will have values attached to each item indicating their "Value" level

    TEMP
001 NAME1]0001
002 ADDRESS1]0001
003 PHONE1]0001
004 NAME2]0001
005 ADDRESS2]0001
006 PHONE2-1]0001
007 NAME2]0002
008 ADDRESS2]0002
009 PHONE2-2]0002
010 NAME2]0003
011 ADDRESS2]0003
012 PHONE2-3]0003

Hope this helps

Lance, I think you're right. BY-EXP appears to be the key

Here's another reason I wanted SELECT to do the heavy lifting.  Without it, if I want to use AQL to define the data to select and output, then I have to emulate F, A, and CALL correlatives in basic as I cannot use them directly.  And any subroutine that uses the ACCESS function will have to be written and compiled on the fly since you are not permitted to assign values to ACCESS from a basic program that is not run from AQL.  The whole project gets muddier and muddier if SELECT can't do the work.  I thought BY-EXP would do the trick but it doesn't seem to give me all fields all the time in the right order. I still get unassigned fields and thus the resulting columns are mangled.

Tom,

I don't believe you have described the scope of the project you are trying to accomplish.

Given a bit more detail regarding both the data elements (examples where data is missing or not) and what your output should look like, the group may be able to offer other suggestions.

Past experience has shown that using BY-EXP is tricky/complicated especially when trying to sort the data correctly.

Suppose you have a file like TEMP that has NAME, ADDRESS, and PHONE where NAME and ADDRESS are both single values but PHONE is multi value.

Like so:

TEMP..................... NAME...... ADDRESS... PHONE.....
1                         NAME1      ADDRESS1   PHONE1
2                         NAME2      ADDRESS2   PHONE2-1
                                                PHONE2-2
                                                PHONE2-3

If you

SELECT TEMP NAME ADDRESS

then you will get a list where each record supplies two items to the list, namely the NAME as one item and ADDRESS as another item:

001 NAME1
002 ADDRESS1
003 NAME2
004 ADDRESS2

However, if you

SELECT TEMP NAME ADDRESS PHONE

then you get at minimum 3 elements per item, but if PHONE happens to have mulitple values, no NAME or ADDRESS is supplied to go with each value.

001 NAME1
002 ADDRESS1
003 PHONE1
004 NAME2
005 ADDRESS2
006 PHONE2-1
007 PHONE2-2
008 PHONE2-3

How can I get blanks inserted for NAME and ADDRESS when PHONE has multiple values?

Can it be done?

Hi Tom,

I think I understand what you are doing; and why you want to use SELECT; but it simply will not work. If I am correct; you want a generic version that shall always work even if you do not know what type of dictionary item is involved in the ACCESS sentence; AND you want the resultant value of that item so it can be manipulated; but you need those values in some structured manner.

What you were hoping for was since SELECT returns all the values of all the fields you hoped it would be structured as well. Sadly; it is not. It is dead-end.

However, all is not lost since you can run any tcl sentence and capture the output. A bit like screen-scraping, if you will. Therefore  issues like CALLs, conversions, correlatives etc become moot. Check the manual for the right modifiers to use on your TCL command but one way [which I agree would be slow, but maybe that's ok] would be to capture the output of each dictionary item singly; for each d3 record you are processing.

I've done this before; but shall not write the code for you but in essence you need two things:

1. A list of record keys; and
2. A list of dictionary names you need per record.

Your routine would loop through the record keys; and then loop through the dictionary names you want executing a like: LIST <filename> '<recordkey>' <dictionaryname> (<suitable options to suppress everything except the data field>. Of course that would be executed and CAPTUREd where the capture would contain the resulting data. You would have to examine it for mv's; and work out the necessary EXCEL bits so it will appear as a list in Excel.

Conceptually; you could do that all in the one EXECUTE I suppose; parsing it for the various output per dictionary 'column'.

But the method described should get you close.

System's like Quovadox's Hostaccess provide rotuines for d3 and handle of this for you; which is another solution. Perhaps contact them? Google RogueWave. I expect a bunch of other Terminal Emulators would do the same. Accuterm - I'm looking at you.

So my final comment is: perhaps you are trying to re-invent the wheel; when an off-the-shelf product could save you grief?

Let me know how you go.

Or maybe I'm on the wrong track???

Cheers,

Suppose you have a file like TEMP that has NAME, ADDRESS, and PHONE where NAME and ADDRESS are both single values but PHONE is multi value.

Like so:

TEMP..................... NAME...... ADDRESS... PHONE.....
1                         NAME1      ADDRESS1   PHONE1
2                         NAME2      ADDRESS2   PHONE2-1
                                                PHONE2-2
                                                PHONE2-3

If you

SELECT TEMP NAME ADDRESS

then you will get a list where each record supplies two items to the list, namely the NAME as one item and ADDRESS as another item:

001 NAME1
002 ADDRESS1
003 NAME2
004 ADDRESS2

However, if you

SELECT TEMP NAME ADDRESS PHONE

then you get at minimum 3 elements per item, but if PHONE happens to have mulitple values, no NAME or ADDRESS is supplied to go with each value.

001 NAME1
002 ADDRESS1
003 PHONE1
004 NAME2
005 ADDRESS2
006 PHONE2-1
007 PHONE2-2
008 PHONE2-3

How can I get blanks inserted for NAME and ADDRESS when PHONE has multiple values?

Can it be done?

Tom,

Assuming this: 

SELECT TEMP BY 0
LIST TEMP NAME ADDRESS PHONE

With normal output looking like this:

Are you hoping to have the data look like this in Excel?

Option 1

or this:

Option 2

or this:

Option 3

or this as tab delimited text file:

Hi Lance,

I would be looking for option 1, the output that most closely matches the printed report.  ID-SUPP should give option 2.  Delimiter doesn't really matter here. I'm using comma (,) with quotes (") where required per the CSV standards.

Suppose you have a file like TEMP that has NAME, ADDRESS, and PHONE where NAME and ADDRESS are both single values but PHONE is multi value.

Like so:

TEMP..................... NAME...... ADDRESS... PHONE.....
1                         NAME1      ADDRESS1   PHONE1
2                         NAME2      ADDRESS2   PHONE2-1
                                                PHONE2-2
                                                PHONE2-3

If you

SELECT TEMP NAME ADDRESS

then you will get a list where each record supplies two items to the list, namely the NAME as one item and ADDRESS as another item:

001 NAME1
002 ADDRESS1
003 NAME2
004 ADDRESS2

However, if you

SELECT TEMP NAME ADDRESS PHONE

then you get at minimum 3 elements per item, but if PHONE happens to have mulitple values, no NAME or ADDRESS is supplied to go with each value.

001 NAME1
002 ADDRESS1
003 PHONE1
004 NAME2
005 ADDRESS2
006 PHONE2-1
007 PHONE2-2
008 PHONE2-3

How can I get blanks inserted for NAME and ADDRESS when PHONE has multiple values?

Can it be done?

Hi Tom,

One way I can think of is to use an I-Type dictionary to remove the multivalues in the phone attribute and replace it with some other character such as '|'

0001: I
0002: change(@record<3>,@vm,'|')
0003: 
0004: PHONE2
0005: 30L
0006: S

Then your select list would end up like this:

0001: NAME1
0002: ADDRESS1
0003: PHONE1
0004: NAME2
0005: ADDRESS2
0006: PHONE2-1|PHONE2-2|PHONE2-3

If you're wanting to consume it with excel then perhaps also consider converting it to a csv using EVAL?

SELECT TEMP SAVING EVAL \\ID:',':NAME:',':ADDRESS:',':PHONE2\\

Resulting in

0001: 1,NAME1,ADDRESS1,PHONE1
0002: 2,NAME2,ADDRESS2,PHONE2-1|PHONE2-2|PHONE2-3

Cheers,
Peter

------------------------------
Peter Cheney
Developer and Systems Superstar
Firstmac
Brisbane Qld Australia
------------------------------

Peter, Unfortunately, we don't have I-type descriptors in D3.

However, for anyone interested in the real solution, D3 does have a way to create tab delimited report data and respects multivalue properly.

Copy the LIST and/or SORT verbs to new items, say TABLIST and TABSORT.  Edit the new item and add 53 to the multi valued list of modes on attribute 2.  For D3/Linux 10.2, that would look like:

TABLIST
top
.P
001 va
002 aa]57]53
003 b
eoi 003
.EX
[220] 'TABLIST' exited
TABSORT
top
.P
001 va
002 aa]5d]53
003 b
eoi 003

Now using these commands instead will produce tab delimited data with multi values already separated and I can manipulate the data as need to produce the output however I want.