Re: Need help with SQL selection statement.

Create and use a temporary table to store the values of your deduction query and then perform the main query.

Tom

>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 Mon Feb 06 1995 - 00:30:32 CET