Re: Creating sequences.

carver_at_frodo.okcu.edu (Lance Carver - Oklahoma City University) wrote:

>Hello,
>
> I am trying to reinitialize a sequence number, depending on the change
>of a date. The following is the code I am using, however it keeps erroring
>out on the select statement. Can anyone help???
>
>create sequence job_sequence_num
> increment by 1
> maxvalue (select distinct job_num_yy||job_num_mm||job_num_dd
> from job_log_tbl
> order byh job_num_yy||job_num_mm||job_num_dd;)
> minvalue 1
> cycle;

I don't think that's a legal syntax. Even if it were, you'd want a single value for the maxvalue yet your returning a sorted result set. I'm assuming you want the max yymmdd from the select.

try something like this (standard untested code disclaimer applies)...

set feedback off
set pages 0
spool crseq.sql
column x1 format a80
select

  'create sequence job_sequence_num' x1,
  '  increment by 1' x1,
  '  maxvalue '||max(job_num_yy*10000+job_num_mm*100+job_num_dd) x1,
  '  minvalue 1' x1,
  '  cycle;'

spool off
set pages 24
_at_crseq

--
Chuck Hamilton
chuckh_at_ix.netcom.com

If at first you don't succeed, skydiving isn't for you.