I suppose I'm stating the obvious but you could always rewrite the select
statement...
Brian Schalme wrote in message <3714E815.A5EE66B3_at_mbnet.mb.ca>...
> Folks:
>
> Do subqueries really work in PL/SQL? For example when I try to compile
>this procedure:
>
>create or replace package body brian_test is
>procedure uno is
>BEGIN
>SELECT NVL(le.lab_key,' '),
> NVL(le.reg_hours_calc,0),
> NVL(labd.adj_code,' '),
> le.emp_no, le.abbrev_name, le.dept, le.attendance_dept
>FROM (SELECT labm.lab_key,
> labm.reg_hours_calc,
> em.emp_no, em.abbrev_name, em.dept,
> em.attendance_dept
> FROM labm, emp_mstr em
> WHERE SUBSTR(labm.lab_key(+),11) = em.emp_no) le,
> labd
>WHERE le.lab_key = labd.lab_key (+);
>end;
>end;
>/
>show errors
>exit
>
> I get the error message "PLS-00320: the declaration of the type of this
>expression is invalid" on the "(SELECT..." statement (the subquery). Now
>nothing is mis-spelled because I can take the guts of this and run it in
>SQL*Plus - it works fine. And I can see no forward references here.
>
> I try to run this in an MF COBOL program and the precompiler kicks out
>the same message. (procob...sqlcheck=semantics...).
>
> Environment:
> HP-UX 10.20
> Oracle Server 7.3.2.3.0
> PL/SQL 2.3.2.3.0
> SQL*Plus 3.3.2.0.0
> Pro*COBOL 1.8.2.0.0
>
> TIA
> Brian Schalme
> bschalme_at_mbnet.mb.ca
Received on Thu Apr 15 1999 - 15:47:15 CDT
