Re: newbie ProC insertion question

In article <3h16a0$il_at_homer.alpha.net> sahmad_at_mfa.com "Saad Ahmad" writes:

:
: I think this would produce a cross product. To fix it change the
: select with
: select p.emp_id, emp.surnam,
: (sum(p.amount)/(count(p.rowid)/count( distinct p.rowid))) -
: (sum(d.amount)/(count(d.rowid)/count( distinct d.rowid)))
:
: Try it. I think it would do it
:

Very nice: but this can be made more elegant as:

          sum(p.amount)/count(distinct d.rowid) - 
          sum(d.amount)/count(distinct p.rowid)

      since count(p.rowid) = count(*) = 
            count(distinct p.rowid) * count(distinct d.rowid)

Alternatively, you could explain it by saying that in the cross-product each 'P' row appears d times, and each 'D' row appears p times ...

(BTW, I suppose count(distinct nvl(rowid,'00000000.0000.0000')) would be best if outer joins had to be used.)

-- 
Jonathan Lewis