Re: Error in Subquery in PL/SQL Procedure

  Answers permeating article.

In article <3714E815.A5EE66B3_at_mbnet.mb.ca>,   Brian Schalme <bschalme_at_mbnet.mb.ca> wrote:
> 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,

  You mean, select the table name in the FROM clause? No, you can't do that. Subqueries are intended to work in WHERE clauses. I think you have misunderstood something here. You probably should do one of the following:

1. put the subquery in the WHERE clause, doing an AND with the WHERE clause you already have;
2. create a view as select the subquery and use the view in your main query.

    Any doubts, reply to this post.

           Eduardo Pimenta.

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

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 15 1999 - 11:42:51 CDT