Re: Q: select with set operators and brackets

From: Jörn Fieg <_at_wmd.de>
Date: Fri, 19 Nov 1999 20:42:47 +0100
Message-ID: <3835A837.95F7DAEF@wmd.de>

Hi!

Gombos Bertalan wrote:

> Hi all!
>
> I would like to issue the following SELECT in PL/SQL:
> (SELECT 1 FROM dual MINUS
> SELECT 2 FROM dual)
> UNION
> (SELECT 3 FROM dual MINUS
> SELECT 4 FROM dual)
>
> How I can do it? It works in SQL but (SELECT 1 INTO x ... ) doesn't work in
> PL/SQL.
>
> Thanks a lot,
> --
> G o m b o s B e r t a l a n
> system engineer, Oracle developer
> mailto:bgombos_at_freemail.c3.hu

First, if you use (SELECT 1 INTO x ... ) you'll try to fetch multiple rows into a single variable. That won't work at all in pl/sql. So you are in need of cursor.

To use a cursor in your example, you'll need an alias in the first selection of the query to name column. If you use table colums in the query, the alias might not be necessary.

So it should look like this:

begin
 declare
  cursor cur_a is
    (SELECT 1 alias_n FROM dual MINUS

       SELECT 2 FROM dual)
    UNION
    (SELECT 3 FROM dual MINUS

       SELECT 4 FROM dual);
 begin
  for row_a in cur_a loop
   ...
  end loop;
 end;
end;
/

--

---

Jörn Fieg                                               WMD GmbH, Hamburg /

Germany
to replay, delete >remove_this< from the adress Received on Fri Nov 19 1999 - 13:42:47 CST