Re: nesting select statments

Hi Bruce,

> Is it possible to nest select statments? eg
> select something, something, something, (select something from
> something)
> from something

Out of my expirience and against all other replies to your post, it is not possible to nest a statement in the way you showed. That is you are not able to have a select statment as part of the column list returned by the query.
On the other hand this is absoluty logical because how should the query behave when theres is more then one row returned by a "column" query.

Although I know that it is nosense in most situations, there some queries where I would have found it very usefull. But most of them could be solved
either by using from listen subqueries or using functions.

That means you can to the following :

select col1, col2, col3, mypackage.myspecialfunction( col1 ) from some_table ;

Where myspecialfunction resides in a PL/SQL Package named mypackage an may look like this
package specification mypackage
is

  myspecialfunction( param1 in varchar2 ) return varchar2;   pragma restrict_references( myspecialfunction, WNDS,WNPS, RNPS ) ;

package body mypackage
is

 myspecialfunction( param1 in varchar2 ) return varchar2  is
  cursor c_d is select something from something where something = PARAM1 ;
  d c_d%ROWTYPE;
 begin
  open c_d ;
  fetch c_d into d ;
  close c_d ;

  return d.something;

 end ;

end ;

regards
 Robo.

Received on Sat Jun 13 1998 - 14:57:00 CDT