D3 and mvBase

 View Only
Expand all | Collapse all

SELECT fields into a list with some multivalues

  • 1.  SELECT fields into a list with some multivalues

    Posted 04-10-2024 11:57

    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 Marracci
    General Manager
    Aircraft Spruce
    corona CA US
    ------------------------------


  • 2.  RE: SELECT fields into a list with some multivalues

    PARTNER
    Posted 04-11-2024 04:31

    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,



    ------------------------------
    David Knight
    Senior Software Engineer
    H3O Business Technologies Limited
    ------------------------------



  • 3.  RE: SELECT fields into a list with some multivalues

    Posted 04-11-2024 11:05

    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



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



  • 4.  RE: SELECT fields into a list with some multivalues

    Posted 04-11-2024 11:19
    Edited by Tom Marracci 04-11-2024 11:26

    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
    ------------------------------



  • 5.  RE: SELECT fields into a list with some multivalues

    Posted 04-11-2024 11:36
    Edited by M Arcus1 04-11-2024 11:37

    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
    ------------------------------



  • 6.  RE: SELECT fields into a list with some multivalues

    Posted 04-11-2024 11:49

    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?



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



  • 7.  RE: SELECT fields into a list with some multivalues

    Posted 04-11-2024 11:59

    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 Rhodes
    marcus1@thinqware.com
    ------------------------------



  • 8.  RE: SELECT fields into a list with some multivalues

    Posted 04-11-2024 12:16

    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.



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