Re: newbie ProC insertion question

From: Saad Ahmad <sahmad_at_mfa.com>
Date: 5 Feb 1995 00:32:00 GMT
Message-ID: <3h16a0$il_at_homer.alpha.net>

Steve Frampton (frampton_at_vicuna.ocunix.on.ca) wrote:

> select pay.employee_id, emp.surname,
> sum(pay.amount)-(select sum(ded.amount)
> from pay_records ded
> where ded.employee_id = pay.employee_id
> and ded.code='DEDUCTION')
> from pay_records pay, employee_table emp
> where emp.employee_id = pay.employee_id
> and pay.code='GROSS_PAY'
> and pay.pay_number between '9401' and '9424'
> group by pay.employee_id, emp.surname
> order by emp.surname;

  select p.emp_id, emp.surnam, sum(p.amount) - sum(d.amount)   from pay_records d, pay_records p, employee_table e   where

         d.pay_number between '9401' and '9424' and
         d.code    = 'DEDUCTION' and
         d.emp_id  = e.emp_id and
         p.code = 'GROSS PAY'  and
         p.pay_number between '9401' and '9424' and
         p.emp_id  = e.emp_id

  group by p.emp_id, p.surnam
  order by 2;

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

(you might have to use an outer join, if your data model suggests so)

--
**************************************************************
*                          Saad Ahmad                        *
*                          E-Mail: sahmad_at_mfa.com            *
**************************************************************

Received on Sun Feb 05 1995 - 01:32:00 CET