Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Subtracting from a set of rows/columns
SELECT
CASE WHEN sort_number <>0 THEN COUNT(col1) END COUNT1, CASE WHEN sort_number <>0 THEN COUNT(col2) END COUNT2, CASE WHEN sort_number <>0 THEN COUNT(col3) END COUNT3, CASE WHEN sort_number <>0 THEN COUNT(col4) END COUNT4, CASE WHEN sort_number <>0 THEN COUNT(col5) END COUNT5FROM test_counters
WHERE test_id=:p_test_id;
It will not Count the rows with NULL in col1, col2 etc...
Jan
Martin T. napísal(a):
> 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
Received on Wed Mar 14 2007 - 04:14:47 CDT