(UniVerse) Filter and select from fields of a specific record

[Timothy Morris]

Hi experts,

In a UniVerse RetrieVe statement, I can select records based on a criteria with the SELECT, WITH and WHEN keywords. What if I know which record I want and I just want to select multivalued fields or values in these fields that meet a criteria?

Say I have a data file in UniVerse called TRANSACTIONS that contains transaction information BUT (and this is important) the record ID is an account ID. The first 5 fields of each record contain metadata about the account (e.g. the account holder's names) and each field from 6 onwards is a transaction related to the account holder. Each of these transactions are multivalued fields for storing information about the transaction (e.g. transaction date, transaction amount).

Record 17 might look something like this:

1 = John

2 = Smith

3 = 1 Smith St

4 = Smithtown

5 = USA

6 = 100₂1/1/22 (a $100 transaction from 1/1/22)

8 = 200₂23/01/23 (a $200 transaction from 23/01/22)

9 = 50₂23/01/23 (a $50 transaction from 20/01/22)

Now, let's say I know I want the transactions from this record, but I want to select just the transaction amount from transactions that have a date after 1/1/23. In UniVerse BASIC, I can get the record, loop through fields from 6 until the last field, and select <1,6,1> where <1,6,2> is greater than 1/1/23. I could then store this information in a dynamic array, save the information to a save list or another file etc.

Is there an easier way to select the transaction amount of these transactions with a simple RetrieVe or SQL statement that I could run at TCL of execute from a UniVerse BASIC program? I guess I would need to find a way to explode the fields of a specific record? I had a look at Rocket's documentation but this doesn't seem possible... Can I create I-type fields in the TRANSACTIONS file's dictionary to make this work somehow? 

Thanks,

Tim

[Brian Speirs]

Hi Tim,

This record isn't structured correctly for normal queries. The key structural point is that each <field> should contain data of the same type. Each field can be multi-valued to contain the data for separate transactions.

In your case, you appear to have a date field (which should be stored as a serial date number rather than as a date literal), and a transaction value. So we would normally expect to see something like this:

DATE:   20000 ] 20030 ] 20060    (Dates = 30 Oct 2022, 02 Nov 2022, 02 Dec 2022)

VALUE:  100 ] 200 ] 300

where ] denotes a value mark. I have also added spaces to clarify things. DATE might be field 6, and VALUE might be field 7.

Your dictionary items should have an association ( a PHrase) on line 7 that relates the DATE and VALUE fields so the query processor treats the multi-values as being related to each other (i.e. lines them up in listings).

Finally, your query might then look like this:

    LIST TRANSACTIONS '17' WHEN DATE GT "01 DEC 2022" DATE VALUE

or if you don't know the specific transaction:

    SELECT TRANSACTIONS WITH COUNTRY EQ "USA" AND WITH DATE GT "01 DEC 2022"

    LIST TRANSACTIONS WHEN DATE GT "01 DEC 2022" DATE VALUE

HTH,

Brian

[Wols Lists]

On 24/01/2023 08:07, Brian Speirs wrote:
> Hi Tim,
>
> This record isn't structured correctly for normal queries. The key
> structural point is that each <field> should contain data of the same
> type. Each field can be multi-valued to contain the data for separate
> transactions.
>
> In your case, you appear to have a date field (which should be stored as
> a serial date number rather than as a date literal), and a transaction
> value. So we would normally expect to see something like this:
>
> DATE:   20000 ] 20030 ] 20060    (Dates = 30 Oct 2022, 02 Nov 2022, 02
> Dec 2022)
> VALUE:  100 ] 200 ] 300

Isn't there a command that will create a cross-tab? So given fields like
Timothy's, it will invert them into the correct form for easy querying?
Might need a little BASIC to tidy things up completely ...

>
> where ] denotes a value mark. I have also added spaces to clarify
> things. DATE might be field 6, and VALUE might be field 7.
>
> Your dictionary items should have an association ( a PHrase) on line 7
> that relates the DATE and VALUE fields so the query processor treats the
> multi-values as being related to each other (i.e. lines them up in
> listings).
>
> Finally, your query might then look like this:
>
>     LIST TRANSACTIONS '17' WHEN DATE GT "01 DEC 2022" DATE VALUE
>
> or if you don't know the specific transaction:
>
>     SELECT TRANSACTIONS WITH COUNTRY EQ "USA" AND WITH DATE GT "01 DEC
> 2022"
>     LIST TRANSACTIONS WHEN DATE GT "01 DEC 2022" DATE VALUE
>
> HTH,
>

Aren't there three keywords, WHEN, WITH, and WHERE?

I can never remember which is which, but one requires an exact match on
the field, one will return the entire record if any value matches, and
one will only return the values that match.

> Brian

Cheers,
Wol

[Timothy Morris]

Thank you both! 

Yes, the file is certainly structured in a funky way. It’s older than me though (and I’m in my 30s) so who knows why. 😅

Regarding the dates, they are serialised. I just used date strings as an example. The transactions I’m trying to query have much more data in them too of course than the example.

I’m thinking the most elegant way to query them from a BASIC program is to read the TRANSACTIONS record, and for each field that is a transaction (6 onwards), write them as their own record to a temporary ‘working’ file which I can then query with RetrieVe, then clear the file. That way I get the benefit of using a dictionary too.

The real problem I actually have is I’ve inherited some old code (up to 25 years old) and the code written to query these transactions is very difficult to read and far longer than it needs to be. It appears in many programs too. I’m thinking my solution and using subroutines will make for cleaner and more readable code which is especially important when few people I know of can read the code.

--
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
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/0e1b4333-7bf3-f02a-1da5-cda7e1874a0a%40youngman.org.uk.

[Peter McMurray]

Oh My! You have inherited a disaster. The like of which I have never seen since the Datamail disaster in Melbourne on a Reality circa 1979. Your solution is certainly the best apart from throwing the junk out.
https://www.rocketsoftware.com/brand/rocket-u2/technical-documentation/UniVerse-v11.2.3 
In the Datamail case the record size limit was 32Kb so the best customer was the most frequent used that overstepped the mark that crashed the system every time.

[Timothy Morris]

Goodness gracious, that is a hilarious story Peter! 😂

I think I'll go with writing them to a temporary file so that I can query them elegantly.

Thanks everyone! 👍

[Scott Ballinger]

It may be possible in UV to create I-type dictionary items that return an mv list of the first value of all attributes 6 and above (AMT) and an mv list of the 2nd value of all attributes 6 and above (DATE), then you can query as expected. (I don't think you can do this in D3 because dict subroutines in D3 can't return vm or svm separated values.)

Good luck!

/Scott Ballinger

[Jim Idle]

I don’t suppose that you can start again with this data structure? An open ended set of attributes starting at attribute N is completely wrong basically. Item size limits are not generally at 32K any more, but the you may be able to feel how wrong this structure is because you cannot easily construct a query for it. Without knowing exactly what is being modeled here, it looks to me like you need a separate CUSTOMER and TRANSACTIONS file. CUSTOMER is Attribute 1 through 5 and no more. Then your transaction elements can either be single valued attributes with an item for each transaction (which then does not place any arbitrary limits on how many transactions there are, or I suppose you can have transaction records with multivalues, but you will end up with some large items representing all transactions for a customer.

Individual attributes should all be of the same type, a value at position N should be the same type as one at position N+1 so, either

TX1

001 DATE

002 AMOUNT

003 WHATEVER

Or you can multivalve them and then correlate them. As IO really isn’t a problem these days, I would use one item per transaction. You can then even partition the file on year or year+month etc. You can also create simple indexes.

What you have now is basically just wrong I am afraid, as others also seem to be saying. However, if this system is already baked for many years, then you are going to have to jump through lots of hoops to do anything with this data outside of convoluted BASIC programs 😟

Jim

--

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
---
You received this message because you are subscribed to the Google Groups "Pick and MultiValue Databases" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mvdbms+un...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/d7fea37f-f1c4-4e5b-b2b3-c2f62b864d60n%40googlegroups.com.

[Gerd Förthmann]

Create a new transaction file and put a trigger on the old one.
Whenever the old file is updated by any application the trigger will write the same information to the properly structured new file.
No further application changes required.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/0a2b8fec-25c0-4b37-a4e4-77249caa0fb6n%40googlegroups.com.

[Gerd Förthmann]

You could use a trigger that constantly updates your temp file.

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/bf36b2b7-a168-43d3-a743-2f9a8c9e5d9bn%40googlegroups.com.

[Wols Lists]

On 25/01/2023 02:28, Scott Ballinger wrote:
> It may be possible in UV to create I-type dictionary items that return
> an mv list of the first value of all attributes 6 and above (AMT) and an
> mv list of the 2nd value of all attributes 6 and above (DATE), then you
> can query as expected. (I don't think you can do this in D3 because dict
> subroutines in D3 can't return vm or svm separated values.)

Actually, that's incredibly easy ...

If you want to write a bit of BASIC, look at the "SUBR()" call for the
i-descriptor, and when writing the subroutine itself, look at the
@RECORD variable.

Pass the value you're looking for into the subroutine, rip the data out
of @RECORD, and pass it back.

Cheers,
Wol

[Will Johnson]

Yes you are thinking of the Universe TCL command REFORMAT

[Timothy Morris]

I never went further with this until now, but your suggestion works perfectly Wol.

You're right - with an I-type expression, I can execute a subroutine and return whatever I like. Brilliant!

Thank you!