Re: Need help with SQL selection statement.

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 7 Feb 1995 02:27:30 GMT
Message-ID: <3h6lqi$62i_at_dcsun4.us.oracle.com>

gers_at_brisbane.DIALix.oz.au (Thomas Mudd) writes:

How about a view....

create view deduction ( employee_id, amount ) as
select employee_id, sum(ded.amount)
from pay_records ded
where ded.code = 'DEDUCTION'
group by employee_id
/

select pay.employee_id, emp.surname, sum(pay.amount) - ded.amount from pay_records pay, employee_table emp, deduction ded

where emp.employee_id = pay.employee_id
and   emp.employee_id = ded.employee_id
and   pay.code = 'GROSS_PAY'
and   pay.pay_number between '9401' and '9424'

gropu by pay.employee_id, emp.surname, ded.amount order by emp.surname
/

OR...............................

if you have oracle 7.1 installed, you can use pl/sql

create or replace function get_ded( p_empno in number ) return number as

        n number;
begin

	select sum(amount)
    	into n
	from pay_records
	where code = 'DEDUCTION'
	  and employee_id = p_empno;

	return n;

end;
/

select pay.employee_id, emp.surname, sum(pay.amount)-get_ded(pay.employee_id) 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, get_ded(pay.employee_id) order by emp.surname
/

OR...............................

in any version, using good ole decode:

select pay.employee_id, emp.surname,

	sum( decode(pay.code,'GROSS_PAY',pay.amount,0) ) - 
		sum( decode(pay.code,'DEDUCTION',pay.amount,0) ) 

from pay_records pay, employee_table emp

where  emp.employee_id = pay.employee_id
and    pay.code in ( 'GROSS_PAY', 'DEDUCTION' )
and    pay.pay_number between '9401' and '9424'

group by pay.employee_id, emp.surname
order by emp.surname
/

>
>Create and use a temporary table to store the values of your deduction query
>and then perform the main query.
>
>Tom
>-----------------------------------------------------------------------------
>frampton_at_vicuna.ocunix.on.ca (Steve Frampton) writes:
>
>>Hello:
 

>>For quite some time now I've always wanted to do something like:
 

>>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;
 

>>As you can see, I want to total up each employees total pays *for the
>>year*, and deduct total deductions *for the year*, and display the
>>result.
 

>>I can't think of the number of times I've wanted to do something like
>>this! But SQL*Plus won't allow me to.
 

>>How do I get around this sort of thing? Is "new_value" or something
>>like that involved? In the past, I've resigned myself to coding these
>>types of things in RPT, but surely there must be an easier and quicker
>>way to do this just using SQL?
 

>>Any information would be greatly appreciated.
 

>>
>>
>>------------------------------------------------------------
>>Steve Frampton E-mail: <frampton_at_vicuna.ocunix.on.ca>
Received on Tue Feb 07 1995 - 03:27:30 CET