I would do exactly that IF some of our dictionary items did not use CALL to produce the data. Is there a way to call a subroutine that utilizes the ACCESS function from another basic program?
Original Message:
Sent: 04-11-2024 12:27
From: M Arcus1
Subject: SELECT fields into a list with some multivalues
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!
------------------------------
Marcus Rhodes
marcus1@thinqware.com
Original Message:
Sent: 04-11-2024 12:16
From: Tom Marracci
Subject: SELECT fields into a list with some 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.
------------------------------
Tom Marracci
General Manager
Aircraft Spruce
corona CA US
Original Message:
Sent: 04-11-2024 11:58
From: M Arcus1
Subject: SELECT fields into a list with some multivalues
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
Original Message:
Sent: 04-11-2024 11:48
From: Tom Marracci
Subject: SELECT fields into a list with some multivalues
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
Original Message:
Sent: 04-11-2024 11:36
From: M Arcus1
Subject: SELECT fields into a list with some multivalues
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
Original Message:
Sent: 04-11-2024 11:18
From: Tom Marracci
Subject: SELECT fields into a list with some multivalues
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
Original Message:
Sent: 04-11-2024 11:04
From: Tom Marracci
Subject: SELECT fields into a list with some multivalues
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
Original Message:
Sent: 04-11-2024 04:30
From: David Knight
Subject: SELECT fields into a list with some multivalues
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
Original Message:
Sent: 04-10-2024 11:56
From: Tom Marracci
Subject: 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?
------------------------------
Tom Marracci
General Manager
Aircraft Spruce
corona CA US
------------------------------