Re: Need help with SQL selection statement.

From: Randy Shoup <rshoup_at_oracle.com>
Date: 7 Feb 95 12:27:08
Message-ID: <RSHOUP.95Feb7122708_at_berlin.oracle.com>

In article <3h6lqi$62i_at_dcsun4.us.oracle.com> tkyte_at_us.oracle.com (Thomas J Kyte) writes:

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

> How about a view....

[view suggestion deleted]

>   OR...............................

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

[PL/SQL suggestion deleted]

>   OR...............................

> in any version, using good ole decode:

[decode() suggestion deleted]

OR:

  In Oracle 7.1, you can put a query in the FROM clause and alias it as if it were a table or view in the database, so you could do:    

select 	pay.employee_id, emp.surname,  
       	sum(pay.amount)-total_ded 				<=== 
from   	pay_records pay,  
	employee_table emp,  
	(select employee_id, sum(amount) total_ded		<=== 
	 from   pay_records					<=== 
	 where  code='DEDUCTION'				<=== 
	 group by employee_id) ded				<=== 
where  emp.employee_id = pay.employee_id 
and    pay.employee_id = ded.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;    

  This is equivalent to creating a temporary view "ded" for the duration of the query. I believe this syntax is part of the ANSI SQL standard, and was added for compatibility. I have found it *extremely* useful for just this kind of situation.  

  I hope this helps.    

• Randy

  ---

  Randy Shoup (415)506-3148 Oracle Browser Development rshoup_at_oracle.com Oracle Corporation -- Randy Shoup Oracle Corporation rshoup_at_oracle.com

Received on Tue Feb 07 1995 - 12:27:08 CET