using a var in spl alter sequence

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 bkennedy@utah.gov

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

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 > bkennedy@utah.gov > > Install the 'exec' bladelet. This will give you dynamic sql in spl.