All 3 messages in topic - view as tree
From: Bevis Kennedy - view profile
Date: Mon, Dec 4 2006 10:39 pm
Email: "Bevis Kennedy"
I have an application that requires a series of unique 12 digit integers. The left most 4 charaters are the year and the right most 8 are a sequence that gets reset to 1 every January 1. I have created a spl routine that uses a sequence and then a sql restart.
The problem is that the spl returns a syntax error whenever I try to use a variable in the alter sequence sql; the following works the way it's written but fails if I substitute 't_cn' for the literal.
CREATE FUNCTION ps_next_case_number()
RETURNING INT8;
DEFINE t_cn, n_cn INT8;
LET n_cn = ps_case_number.nextval;
LET t_cn = YEAR(CURRENT)||'00000001';
IF (n_cn < t_cn) THEN
ALTER SEQUENCE ps_case_number RESTART WITH 200700000001;
LET n_cn = ps_case_number.nextval;
END IF;
RETURN n_cn;
END FUNCTION;
I would only have to edit this spl routine once a year but it seems to me that spl should be able to handle the new year change.
Bevis Kennedy
Department of Technical Services
cell: (801) 641-8192
fax: (801) 965-4749
bkenn...@utah.gov
Reply » Rate this post: Text for clearing space
From: Serge Rielau - view profile
Date: Tues, Dec 5 2006 2:01 am
Email: Serge Rielau
- Hide quoted text -
- Show quoted text -
Bevis Kennedy wrote:
> I have an application that requires a series of unique 12 digit integers. The left most 4 charaters are the year and the right most 8 are a sequence that gets reset to 1 every January 1. I have created a spl routine that uses a sequence and then a sql restart.
> The problem is that the spl returns a syntax error whenever I try to use a variable in the alter sequence sql; the following works the way it's written but fails if I substitute 't_cn' for the literal.
> CREATE FUNCTION ps_next_case_number()
> RETURNING INT8;
> DEFINE t_cn, n_cn INT8;
> LET n_cn = ps_case_number.nextval;
> LET t_cn = YEAR(CURRENT)||'00000001';
> IF (n_cn < t_cn) THEN
> ALTER SEQUENCE ps_case_number RESTART WITH 200700000001;
> LET n_cn = ps_case_number.nextval;
> END IF;
> RETURN n_cn;
> END FUNCTION;
> I would only have to edit this spl routine once a year but it seems to me that spl should be able to handle the new year change.
I presume IDS supports dynamic SQL? That would be my recommendation in
DB2...
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference
http://www.iiug.org/waiug/present/Forum2006/Forum2006.html
Reply » Rate this post: Text for clearing space
From: Claus Samuelsen - view profile
Date: Tues, Dec 5 2006 11:11 am
Email: Claus Samuelsen
- Hide quoted text -
- Show quoted text -
Bevis Kennedy wrote:
> I have an application that requires a series of unique 12 digit integers. The left most 4 charaters are the year and the right most 8 are a sequence that gets reset to 1 every January 1. I have created a spl routine that uses a sequence and then a sql restart.
> The problem is that the spl returns a syntax error whenever I try to use a variable in the alter sequence sql; the following works the way it's written but fails if I substitute 't_cn' for the literal.
> CREATE FUNCTION ps_next_case_number()
> RETURNING INT8;
> DEFINE t_cn, n_cn INT8;
> LET n_cn = ps_case_number.nextval;
> LET t_cn = YEAR(CURRENT)||'00000001';
> IF (n_cn < t_cn) THEN
> ALTER SEQUENCE ps_case_number RESTART WITH 200700000001;
> LET n_cn = ps_case_number.nextval;
> END IF;
> RETURN n_cn;
> END FUNCTION;
> I would only have to edit this spl routine once a year but it seems to me that spl should be able to handle the new year change.
> Bevis Kennedy
> Department of Technical Services
> cell: (801) 641-8192
> fax: (801) 965-4749
> bkenn...@utah.gov
Install the 'exec' bladelet. This will give you dynamic sql in spl.
Reply » Rate this post: Text for clearing space
End of messages
No comments:
Post a Comment