Re: Checking for existence of a record

I would use SQL and only reference/select indexed fields - this would cause an index lookup only, without fetching the record at all.

And I would try to optimize the SQL select using Visual Explain and create all needed indexes.

But of course I would try to solve the problem using SQL in the first place - maybe with a left join during another SQL operation - this is from my experience always the most performant way to go. One shouldn't replace native-I/O with SQL - one should rethink the whole logic.

But I'm a pure-SQL-guy - I don't use native-I/O if I don't have to.

HTH
Daniel

Am 03.06.2022 um 19:38 schrieb James H. H. Lampert via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

Suppose I have a file that is keyed on a customer number and a date, both keys ascending.

And I want to check for the *existence* of at least one record with the specified customer number and a date *no earlier than* a specified cutoff. The *contents* of that record are of no interest; all I care about is the *existence* of at least one.

I have a KLIST on my file. I plug the customer number into the first field of the KLIST, and the cutoff date into the second.

In my test program, I look for two different customer numbers, one which *does* have at least one record meeting the cutoff date, and one which *does not* have one.

If I SETLL on the KLIST, without actually reading anything, and only looking at %FOUND, then both come back with %FOUND = true, since the customer number *without* a record-of-interest just falls through to the next customer number.

If I CHAIN on the KLIST, then they both come back with %FOUND = false, since the customer number *with* a record-of-interest doesn't have one that exactly matches the date.

I *could* do a READ after the SETLL, and check to see if the customer number matches. Or I *could* do a READE on it, specifying only the customer number, and check %EOF.

Is there anything else that I'm missing?

I'm looking for maximum efficiency on this check, as it's going to have to be run for a few thousand different customer numbers.

--
JHHL
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com