Re: Subtracting from a set of rows/columns

On Mar 14, 6:01 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Martin T. wrote:
> > Greetings,
>
> > The following situation:
> > TABLE test_counters ...
> > SORT_NUMBER | TEST_ID | COUNT1 | COUNT2 | COUNT3 | COUNT4 | COUNT5
>
> > I need to subtract from all counts of a test_id the values of the
> > respective count columns for sort_number zero.
> > (ie: count* = count* - count*_where_sort_number_zero)
>
> > I have implemented it now as follows:
> > ---
> > v_counts test_counter%rowtype;
> > ...
> > SELECT *
> > INTO v_counts
> > FROM test_counters
> > WHERE SORT_NUMBER=0
> > and TEST_ID = :p_new_id;
>
> > UPDATE MEASURES
> > SET COUNT1 = COUNT1 - v_counts.count1,
> > COUNT2 = COUNT2 - v_counts.count2,
> > COUNT3 = COUNT3 - v_counts.count3,
> > COUNT4 = COUNT4 - v_counts.count4,
> > COUNT5 = COUNT5 - v_counts.count5
> > WHERE ORDER_ID = p_new_order_id;
> > ---
>
> > Is there a more elegant way to do it?
>
> > cheers,
> > Martin
>
> Seriously consider a redesign as:
>
> CREATE TABLE test_counters (
> sort_number NUMBER,
> test_id VARCHAR2(30),
> count_id NUMBER(2),
> count_value NUMBER);
>
> Think vertically.
> --
> Daniel A. Morgan

Ed, Daniel,
thanks for the replies.

There are really only 5 different counters (counters as in: Measurement values that represent a number of counted objects.) The table has simply one row per testsample.

Really. I should start writing down all that info in my initial posts :)

thanks anyway!
br,
Martin Received on Wed Mar 14 2007 - 15:18:02 CDT