Re: Stored Procedure (or SQL) to get a value then update it to +1

Sigh...Sometimes I wonder at all the workarounds we have to do just to
satisfy some of those "obscure business requirement"(s) . If nothing else,
they make us think a little bit more (I agree, a sequence number would be a
LOT easier)

Best Regards,


Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Wed, Jul 11, 2012 at 3:16 PM, <rob@xxxxxxxxx> wrote:

And the function could use a sequence number and the current year for the
join. But there's probably some obscure business requirement that the
cutoff be manually done after some other process and not exactly at 12:01
on New Years morning.


Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Luis Rodriguez <luisro58@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>,
Date: 07/11/2012 03:18 PM
Subject: Re: Stored Procedure (or SQL) to get a value then update
it to +1
Sent by: midrange-l-bounces@xxxxxxxxxxxx



Mike,

Instead of a Stored Procedure, make it a function. That way you can, for
example, do a simple table update. Something like:

UPDATE ANOTHER_TABLE SET new_Sequence = NEXTSEQ(0);
A simple skeleton for a function that does what you want would be:
********
DROP FUNCTION NEXTSEQ;

Create Function NEXTSEQ (RESET NUMERIC(04, 0) )
Returns Numeric(04, 0)
Language SQL
Modifies SQL DATA
BEGIN
Declare RetVal Numeric(04,
0);
Select NextKey INTO RetVal From
CONSTANT;
Update CONSTANT Set NextKey = NextKey + 1;
Return(RetVal);
END
********

If reset <> 0 you could update NextKey with the new value, for instance.
That is not defined in this example, but I just wanted to show to how to
code an entry parameter.
Also, you could make it return yy-#### instead of just a number. Define
RetVal as CHAR(07) and code a CONCAT() just before the Return statement.

HTH,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--




On Wed, Jul 11, 2012 at 1:46 PM, Mike Wills <mike@xxxxxxxxxxxx> wrote:

Stored procedures are not strong in this one.;-)

We have a "constant" file that holds the next sequence number for one

of

our applications. I now need to access that from an external

application. I

am hoping I can write a stored procedure to essentially

SELECT nextkey from CONSTANT
UPDATE CONSTANT SET nextkey = nextkey + 1

How would I put this one stored procedure?


--
Mike Wills
http://mikewills.me
--
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.

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



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

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