Universe. I-desc. Subroutine. Finding a record STUMPED

[Dawn Wolthuis]

UV 11.3.2

Attempting an I-desc on a file/dict where there is no foreign key to the @ID of the file where the target record/value is.

The metadata could be simplified with these files/fields

ORDERS

     @ID ABC123 ((

     SIMPLE.FIELD = D

     [I'm stumped on writing this I-desc] SIMPLE.FIELD.CHANGE.DATE

AUDIT

     @ID = WHATEVER

     RECORD.TYPE = ORDERS

     RECORD.ID = ABC123

     FIELD.NAME = SIMPLE.FIELD

     AUDIT.DATE = a date in the past

In a regular (non-I-desc) subroutine, I can write a SELECT query to get the target record and then get the desired value from that record. This has the name of the field hard-coded, and that is sufficient for these purposes.

SELECT AUDIT WITH RECORD.ID = "ABC123" AND WITH RECORD.TYPE = "ORDERS" AND WITH  = FIELD.NAME = "SIMPLE.FIELD" 

However, in an I-desc subroutine, I run into this issue

http://u2-universe-unidata.1073795.n5.nabble.com/Non-SQL-re-entrant-query-calls-are-not-allowed-UV-10-0-2-td42135.html

I'm helping a user. I'm not on a dev team doing more sys-like things such as managing indexes (so you can suggest that I create one, but I'm not likely to do so), etc.

I tried doing a SELECTINDEX on the @ID (recognizing that is the primary index, but I thought I might be able to query that index with this -- it hasn't worked yet bz I don't know a name for that alt-index since it isn't one). There are no alternative indexes on the file at all, and it is unlikely there will be.

I tried a number of things, none of which worked, most of which resulted in the same "Non-SQL re-entrant query calls are not allowed" message.

Any suggestions?  TIA  --Dawn

[Dawn Wolthuis]

In case anyone picks this up as a puzzle during the Grammys (OK, unlikely), I'll add that I can get a result using the BASIC SELECT rather than executing an MV query SELECT and then reading through them all (large file), by just accepting the first record that matched the criteria when doing readnext. However, there will be a list of records and I will need to pick the most recent by date by time. 

When I do a SELECT on the AUDIT file to get the entire thing, I don't have the experience coding in MV BASIC to know a good way to save off data that includes time and date and then sort that or otherwise get the most recent. This is way more complex than if the query SELECT statement worked via an I-desc subroutine (I didn't know that didn't work until I tried it). I think I bit off more than I can chew with this one. It sounds like this is considered a KNOWN BUG for decades -- is that correct?

All suggestions and code examples are welcome.

--Dawn

[Marcus Rhodes]

If I've understood you correctly (and the way you expressed ORDERS' @ID (( seems wrong to me) (Does that indicate that it's multivalued??  You only get one.), the index would have to be on your AUDIT file's RECORD.ID field.  That way, the I-type in ORDERS can SELECT AUDIT WITH RECORD.ID = "@ID" (of the ORDERS record), and the result should be a multivalued list of the AUDIT records' @IDs.

[Wols Lists]

On 14/03/21 23:20, Dawn Wolthuis wrote:
> In case anyone picks this up as a puzzle during the Grammys (OK,
> unlikely), I'll add that I can get a result using the BASIC SELECT
> rather than executing an MV query SELECT and then reading through them
> all (large file), by just accepting the first record that matched the
> criteria when doing readnext. However, there will be a list of records
> and I will need to pick the most recent by date by time.
>
> When I do a SELECT on the AUDIT file to get the entire thing, I don't
> have the experience coding in MV BASIC to know a good way to save off
> data that includes time and date and then sort that or otherwise get the
> most recent. This is way more complex than if the query SELECT statement
> worked via an I-desc subroutine (I didn't know that didn't work until I
> tried it). I think I bit off more than I can chew with this one. It
> sounds like this is considered a KNOWN BUG for decades -- is that correct?
>
> All suggestions and code examples are welcome.

Bearing in mind I've never done anything like this, but does

SELECT file TO 3 ...

work? In an i-descriptor you're quite likely to get an internal select
clobbering the outer working select, so doing the internal select to a
different list might fix that problem ... (the other thing is, EXECUTE
and PERFORM behave differently, so if the select won't EXECUTE, it might
PERFORM instead.)

And then also try

SELECT file TO 3 SAVING @ID TIME DATE

READLIST 3 ...

That will, I think, give you a dynamic array with one record per field,
with @ID, TIME and DATE as values within the field.

Cheers,
Wol

[David Green]

Dawn,

 

There are a lot if issues with this.

 

First the key to the Audit record could be RECORD.TYPE*RECORD.ID if your audit dates are MV. Otherwise you would use RECORD.TYPE*RECORD.ID*DATE*TIME.

 

This would allow easy lookups.

 

If that isn’t possible, like you mentioned, an Index on the Audit file.  You would write an I-Desc like IF RECORD.TYPE = “ORDERS” THEN RECORD.ID ELSE “”  and then don’t index on NULLs.  That way a simple SUBR I-Desc could easily pull back all the orders that matched the record id.

 

Now if neither of those options will work for you, then I would try a preprocess routine to create a temp file with the data you need and with the @ID equal to the order id.  Then pull the data from the temp file when processing.

 

David Green

--
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/CANKEuc7T3xmaqQbt-Rbp0KCbNai%3Dyu6t7VLFtgV60jQPb8E0Ag%40mail.gmail.com.

[Dawn Wolthuis]

Thanks to all of you (Marcus, Wol, and David) - you can tell by the way that I framed it that I'm not an MV BASIC programmer -- I'm just picking up tidbits as needed. I was only indicating the @ID so it was clear that it was a separate field from the others, not a combination of any of them (not a multi-part key, and definitely not a multivalued key -- it is just a generated surrogate key).

After talking with others, I decided to do something I don't usually prefer to do, but given what I now know about I-desc subroutines, I will be storing that actual date on the file where I wanted to put the I-desc. I'll think of it as "materialized derived data" so that I don't feel too guilty about the duplication of that date data. 

Thanks a bunch for your help!    --Dawn

To view this discussion on the web visit https://groups.google.com/d/msgid/mvdbms/003f01d719bf%24d603a510%24820aef30%24%40dagconsulting.com.

[antlists]

On 15/03/2021 17:46, Dawn Wolthuis wrote:
> After talking with others, I decided to do something I don't usually
> prefer to do, but given what I now know about I-desc subroutines, I will
> be storing that actual date on the file where I wanted to put the
> I-desc. I'll think of it as "materialized derived data" so that I don't
> feel too guilty about the duplication of that date data.

Given that audit records are supposed to be immutable, right, that date
is an attribute of the audit? So it belongs in the audit file.

Bit like when you send an invoice, the address(es) become attributes of
the invoice and need to be decoupled from the client, ie they now belong
in the invoice file.

Cheers,
Wol