SQL*Plus: Expressions using inner selects not supported?

From: Steve Frampton <3srf_at_qlink.queensu.FOOBAR.ca>
Date: 1998/02/23
Message-ID: <6cs12m$98t$1@news.gov.on.ca>#1/1

[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 Mon Feb 23 1998 - 00:00:00 CST