Re: Sequences

	/*********************************************************************
	*
	*	demonstration of generating a sequence within parent
	*	with the use of nextval and currval to create the tables
	*	as requested.
	*
	*********************************************************************/

 

create sequence rule_seq
increment by 1
start with 1
maxvalue 400
nocycle
nocache
order ;

begin
  declare

	rule	number ;
	clause	number ;
  begin
	for rule in 1..3 loop
	  insert into rule (rule_id)
	    values (rule_seq.nextval) ;

		/* now, generate sequence within parent */

	  for clause in 1..3 loop
	    insert into rule_clause (rule_id, eval_sequence)
	      select rule_seq.currval, nvl (max(eval_sequence),0) + 1  from rule_clause;

	  end loop ; /* for clause */
	end loop ;  /* for rule */

For use in triggers RULE_ID comes from the parent block if in a form or it comes as an argument.

Richard W. Harris (rharris_at_ties.org) wrote:
:> In article <Pine.HPP.3.91.960212112758.7623A-100000_at_mailgate>,
:> Heather Dyson <dyson_at_template.com> wrote:
:> >
:> >I have two tables RULE and RULE_CLAUSE.
:> >
:> >The RULE table has a primary key of RULE_ID.
:> >The RULE_CLAUSE table has a primary key of RULE_ID (a foreign key to
:> >RULE) and EVAL_SEQUENCE.
:> >
:> >RULE RULE_CLAUSE
:> >------- -----------
:> >RULE_ID NUMBER(8) RULE_ID (FK) NUMBER(8)
:> > EVAL_SEQUENCE NUMBER(2)
:> >
:> >
:> >I want to generate the RULE_ID with a sequence. The question
:> >is can I also generate the EVAL_SEQUENCE with a sequence knowing
:> >that the EVAL_SEQUENCE has to be based on the RULE_ID? In
:> >other words what is the easiest way to generate the EVAL_SEQUENCE
:> >to start at 1 for each unique RULE_ID?
:> >
:> >So I would end up with records like
:> >
:> >RULE table:
:> >RULE_ID
:> >-------
:> >1
:> >2
:> >3
:> >
:> >RULE_CLAUSE table:
:> >RULE_ID EVAL_SEQUENCE
:> >------- -------------
:> >1 1
:> >1 2
:> >1 3
:> >2 1
:> >2 2
:> >2 3
:> >3 1
:> >3 2
:> >3 3
:> >
:> >
:> >Thanks for any help.
:> >
:> >Heather

--
This posting represents the personal opinions of the author. It is not the
official opinion or policy of the author's employer. Warranty expired when you
opened this article and I will not be responsible for its contents or use.