|
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.;-)application. I
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
am hoping I can write a stored procedure to essentiallylist
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
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.