Re: Rows NOT found in cursor search

[Jose Perez <joseenocperez.jp@xxxxxxxxx>]

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