SELECT puzzle

• Quoting Willyk_at_Kbigate.Stgt.Sub.Org to All dated 04-06-94 ***
  > What I want to get is
  > sum(B3) + sum(C3)
  > for every record of A1 in table A. Records in B which occur in C
  > should not count; this means, I have to use sub-select.
  

Huh? It sounds like your primary problem is poor data modeling, but we can set that aside for the moment, and concentrate on the query:

select a1, sum(nvl(decode(nvl(c3,b3),c3,0),0), sum(nvl(c3,0)) from a, b, c  where a1=c1(*) and a1=b1(*) group by a1;

Here's how it works: first, since we're doing an outer join, we have to translate all returned NULL values to 0 for use in the sum function. Second, the NVL function in the first sum statement tests to see if c3 is null (assuming that there are no null value in the table, this indicates that no row was returned for c, therefore we should add the value of b.) The decode tests to see if the resultant value is equal to c3, and if so, 0 is added to the sum. If c3 and b3 return no rows, then 0 should be added to the sum.

Is it faster? Probably, especially if you're using clustered indexes, but you'll have to test it, preferably using EXPLAIN PLAN. However, I suggest the following two long-term approaches:
1) Remodel your data so that this arrangement is no longer necessary. 2) Upgrade to Oracle 7.0.

Michael Stowe
Constellation Engineering Received on Sun Apr 10 1994 - 12:43:00 CEST