-----Original Message-----
From: Thomas Garvey [mailto:tgarvey@xxxxxxxxxxx]
Sent: Thursday, April 12, 2012 12:13 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: SQL Update value in RPG?
I can't use the SEQUENCE or IDENTITY phrase. My understanding is that their
scope is table wide, and my intention is subsidiary to other fields. MY use
would be similar to a count of how many times a record has been changed. So,
the counter is not a key value or a sequence that is unique across the
table. I'd like to be able to execute the SQL UPDATE, incrementing the
counter field (multiple programs will be able to update this counter), and
when the counter has reached a variable limit, subsequent logic would send a
message to a user that the limit has been reached.
I like the idea of NEXT VALUE though, and have other applications where I
might use that in the future.
Thanks.
Tom Garvey
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Wednesday, April 11, 2012 5:22 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: SQL Update value in RPG?
On 11-Apr-2012 09:47 , Thomas Garvey wrote:
I'm using embedded SQL to update a counter in a database field, but
would also like to know the new value of that counter without having
to retrieve the newly updated record.
For example, using embedded SQL in my RPGLE, as follows...
exec sql
update fileX
set counter = counter + 1
where keyfield = :something
I need to be able to control subsequent logic if the counter has
reached a certain limit. I'd like to not have to re-retrieve the
record to find out if the limit has been reached.
Anyone have any ideas?
Perhaps possible, to implement the limit via either the data type\length
or a constraint, and then the SQLCODE or SQLSTATE can inform of the detected
condition. That of course is slightly different, because the limit would
already have been reached, as contrasted with knowing the next increment
would encounter the limit; i.e. "subsequent logic" would not be the same as
for the posited scenario.
If a SEQUENCE could possibly be used instead, SET the NEXT VALUE for the
SEQUENCE into a variable. That value would then be used both "to control
subsequence logic if the counter has reached a certain limit"
and to set the COUNTER [instead of using the arithmetic calculation
COUNTER+1]; e.g.:
set :NxtInSeq=NEXT VALUE FOR CounterSeq ;
update fileX set counter=:nxtinseq where keyfield=:something ;
An IDENTITY column has already been mentioned.
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
