Re: Sequences

From: Richard W. Harris <rharris_at_ties.org>
Date: 1996/02/14
Message-ID: <4frf1p$ksg_at_dns.city-net.com>#1/1

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

Hmmmm...

You have two alternatives, unless somebody out there knows of a better way:

First, a couple of assumptions. You mention using sequences, so I will assume that the idea is that these records are ordered by how they are inserted, and that the values should be automatically assigned. In other words, the first record inserted into the RULE table will be RULE_ID 1, the next will be assigned 2, and so on. For the RULE_CLAUSE table, you will be giving the RULE_ID, and the EVAL_SEQUENCE is assigned based on the order it is inserted into that table.

1. You can use two sequences, and drop the requirement that the EVAL_SEQUENCE go 1,2,3... for each RULE_ID. You would have a table something like:

RULE_CLAUSE RULE_ID EVAL_SEQUENCE
------- -------------

1             1
1             2
1             3
2             4
2             5
2             6

Since you will be getting then in order by RULE_ID, EVAL_SEQUENCE, it really doesn't matter if the EVAL_SEQUENCEs for RULE_ID 100 are 1,2,3 or 533,534,545.

2. If you must have them reset the counter for each new RULE_ID, then I would suggest an INSERT TRIGGER (for each row). This trigger would do something like

SELECT MAX(EVAL_SEQUENCE) FROM RULE_CLUASE WHERE RULE_ID = :new.rule_id;

If there are no records found, set the EVAL_SEQUENCE for this record to 1. If there is a max found, set the EVAL_SEQUENCE to max+1;

Are you sure you need to do this?

• Rick

  ---

Richard Harris - Database Administrator, Lycos, Inc. E-Mail: rharris_at_ties.org HTML: http://www.city-net.com/~rharris

         rharris_at_lycos.com Received on Wed Feb 14 1996 - 00:00:00 CET