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

From: David Vanidour <dvanidou_at_psc-cfp.gc.ca>
Date: 1998/02/24
Message-ID: <34F32AD2.2BEC@psc-cfp.gc.ca>#1/1

I don't know Steve, why should that be supported when you could write it like this...

select emp.employee_id,
       emp.surname,
       emp.first_name,
       emp.second_name,
       (to_number(sysdate - emp.birth_date)/365.25) + 
         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)) factor

  from ec_employee emp, ec_employee_positions pos, ec_employee_sen_serv sen
 where emp.employee_id = pos.employee_id    and nvl(pos.position_end_date,sysdate) >= sysdate

   and pos.record_status = 'A'
   and sen.employee_id = emp.employee_id
   and sen.sen_serv_code = 'SENIORTY' 

 group by emp.surname, emp.first_name, emp.second_name having factor >= 80
 order by emp.surname, emp.first_name, emp.second_name;

Now I'll admit this may not be quite syntactically correct so you may have to tweak it a little to get it to run but it should get you pointed in the right direction.

David.

PS. Note: Stop converting date fields to/from character. First of all you are setting yourself up for a Y2K fall and secondly it isn't required.

Steve Frampton wrote:
>
> [From: header modified to thwart e-mail spam. See .sig for details]
>
> Hello:
>
> In SQL*Plus (3.1.3.1.3) under Oracle7 (7.1.3.2.0), something like:
>
> select 5 +
> (select 10 from dual)
> from dual;
>
> does not appear to work. Instead of getting an answer of 15, I get:
>
> ERROR at line 1:
> ORA-00936: missing expression
>
> This does not fare well for real-world applications. I developed a
> quick script to determine whether any of our employees are at or above
> their 85 factor (a combination of their age and their seniority service)
> using the technique above, and it doesn't work. To wit:
>
> select emp.employee_id, emp.surname, emp.first_name, emp.second_name
> from ec_employee emp, ec_employee_positions pos
> where to_number(sysdate-to_date(emp.birth_date,'YYMMDD'))/365.25 +
> (
> select 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))
> from ec_employee_sen_serv sen
> where sen.employee_id = emp.employee_id
> and sen.sen_serv_code = 'SENIORTY'
> ) >= 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;
>
> I'm not sure how else I can code this, at least not without putting
> considerable effort into putting intermediate values into temporary
> tables.
>
> This *is* something that *should* be supported, isn't it?
>
> Thanks in advance. E-mail replies are not required.
>
> ----------------< LINUX: The choice of a GNU generation. >----------------
> Steve Frampton <3srf(@)qlink.queensu.ca> http://qlink.queensu.ca/~3srf
> ----------- Please remove .FOOBAR from address before replying. ----------
  Received on Tue Feb 24 1998 - 00:00:00 CST