Re: Subtracting from a set of rows/columns

On Mar 14, 3:03 am, "Martin T." <bilbothebagginsb..._at_freenet.de> 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

A better table design might enable a more elegant program design. With the table you have, your code looks about as good as it can get.

Ed Received on Wed Mar 14 2007 - 11:55:48 CDT