Using sum in nested select

[wellhole]

Hi,

I'm trying to use use a sum value from table2 in my nested select, but I can't seem to figure a way to not have to calculate it twice.

select a, b, c, d, e,
  ( select
      name
    from
      table1
    where
      minv <= (select sum(num) from table2 where a != b)
    and
      maxv >= (select sum(num) from table2 where a != b)  )
from
  table2, table3, table4
where
  ............... a = b .........
 

Is there some way to do this efficiently?

[docdiesel]

Hi,

first, I added some code tags to your sql statement for better readability. Second, you could store the sum in another table:

delete from mytables.storedsum;
insert into mytables.storedsum
  select sum(col1)
  from mytables.table2
  where ... ;
 
select a, b,
  ( select  name
    from  table2
    where a<=(select mysum from mytables.storedsum)
    and   b<=(select mysum from mytables.storedsum)
  )
from ...

This way you could give DB2 a short query. To use a variable or something similar instead, I see no way. Writing a stored procedure could give you more possibilities, on the other hand.

Regards,

Bernd

[wellhole]

I wish it was as simple as a single sum value.

If I'm properly interpreting your approach, you're suggesting that I use a temp table to store the calculations and pull each sum from that table instead. I hope that'll reduce my timings, because table2 is over a million records and the temp table is going to have to store a few hundred sums.

Thanks Bernd. I'll give it a try.