RE: Checking for existence of a record

An index only access (all columns specified in the SELECT are found in the key), will not cause the record to be read.
If you get an index only access Visual Explain will not show a table probe.

SELECT 1 into YourVar
From YourTable where key1 = 'KeyVal1' and Key2 = 'KeyVal2' ....

With native I/O you can position SETLL without reading the row.

Not sure why you need this
... but just in case if you want to insert a row in an SQL routine and check before if the row exists, you may just code a continue handler for the duplicate error message and handle it. In your routine you just execute the insert statement.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i


"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Freitag, 3. Juni 2022 19:52
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: 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

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