Re: Rows NOT found in cursor search

As Paul said in first instance create a table with all customer to search
Then you can read your new table through a cursor and determine one by one
which exist and which doesn't and do what need to be done on one or the
other, other solution could be create your table with two fields customer
and foundflag, then update foundflag based if client found or not found,
then with a cursor select just found customer and do what need to be done
through foundcursor, then other cursor with notfound and do what need to be
done through notfound cursor but that's extra work, so is up to you.

On Tue, Jul 14, 2020, 3:53 PM Paul Therrien <paultherrien@xxxxxxxxxxxxxxxxxx>
wrote:

First thing, turn you list of customers into a separate table.


Paul

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Javier
Sanchez
Sent: Tuesday, July 14, 2020 3:48 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Rows NOT found in cursor search

Good day everyone.

I need to update a table whose key is a customer number, say a
NUMERIC(9,0).
I have to search with a cursor, for example like this:

EXEC SQL DECLARE CSR0001 CURSOR FOR
SELECT * FROM CUSTOMER_MASTER WHERE CUSTOMER_NUMBER IN ( 123456789,
234567890,
345678901,....)

and assume that it is a literal very large list of customer numbers, say
over 20,000.

I need to do a simple update of a column for some status for each found
row.
But I need also to report which row was NOT found in this search in order
to
do some other task.

I suspect I should use a MERGE for this, but if I only use this cursor
approach, how does one know which rows are NOT found within the literal
list
while the engine is searching?

Is there a kind of "exit" event to capture when the engine DOES NOT find a
matching row?

Thanks in advance.

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