Re: SQL*Plus: Expressions using inner selects not supported?

From: Kirill Richine <kirill_at_cs.ualberta.ca>
Date: 1998/02/23
Message-ID: <6csila$el7$1@scapa.cs.ualberta.ca>#1/1

Steve Frampton (3srf_at_qlink.queensu.FOOBAR.ca) wrote:
: I'm not sure how else I can code this, at least not without putting
: considerable effort into putting intermediate values into temporary
: tables.

Try this:
 select emp.employee_id, emp.surname, emp.first_name, emp.second_name  from ec_employee emp, ec_employee_positions pos,

    (select employee_id employee_id,

     sum(decode(sen.emp_group_code,'CLEANFT',sen.sen_serv_value/10,
		 'CLEANPT',sen.sen_serv_value/10,
	         'CLRTECH2',sen.sen_serv_value/10,
		 'EDASSIST',sen.sen_serv_value/10,
	         'SCHSEC',sen.sen_serv_value/10,
	         'SUPPORT2',sen.sen_serv_value/10,
	         sen.sen_serv_value/12)) decoded_sum
     from ec_employee_sen_serv sen
     where sen.sen_serv_code = 'SENIORITY'
     group by employee_id) sen_view

  where sen_view.employee_id = emp.employee_id   and to_number(sysdate-to_date(emp.birth_date,'YYMMDD'))/365.25 +

     sen_view.decoded_sum >= 80
  and emp.employee_id = pos.employee_id  and nvl(pos.position_end_date,to_char(sysdate,'YYMMDD')) >=

                                          to_char(sysdate,'YYMMDD')
 and pos.record_status = 'A'
  order by emp.surname, emp.first_name, emp.second_name;

  There may be some errors, but I hope you get the idea. Received on Mon Feb 23 1998 - 00:00:00 CST