Martin T. wrote:
> 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
Even if there will never be a sixth ... experience teaches otherwise ...
it is still a design already causing problems. I recommend fixing it.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Wed Mar 14 2007 - 21:04:22 CDT
