Re: Q: select with set operators and brackets

Hi!

It should also work by cursor, just want to perform only a check.

You open a cursor, that will compare your tables by using the significant columns only (special cases see below).
Fetch the first row of the cursor and perform a check, if the row was found. If at least one row is found, the tables are not equal. At this point, you got the result and there is no need to watch the other rows. Don't forget to close the cursor at last.

begin
 declare
  cursor cur_cmp is
    (SELECT col_1, col_2, ... FROM table_a MINUS

       SELECT col_1, col_2, ... FROM table_b)     UNION
    (SELECT col_1, col_2, ... FROM table_b MINUS

       SELECT col_1, col_2, ... FROM table_a);    row_cmp cur_cmp%ROWTYPE;
 begin
   open (cur_cmp); -- open cursor
   fetch (cur_cmp) into row_cmp; -- try to get the first row    if (cur_cmp%FOUND) -- check if row was found    then

• at this point, the tables are not equal .... end if; close (cur_cmp); -- close cursor end; end; /

Special cases for compare:
- If all columns are significant and the column order is equal in both tables, you can declare your cursor like this:
  cursor cur_cmp is
    (SELECT * FROM table_a MINUS

       SELECT * FROM table_b)
    UNION
    (SELECT * FROM table_b MINUS

       SELECT * FROM table_a);

• If the names of the columns are different, the column names of the first select are used.
• If you want to use aliases for the columns, declare them at the first selection in the statement like this: (SELECT col_1 alias_1, col_2 alias_2, ... FROM table_a MINUS SELECT col_1, col_2, ... FROM table_b) UNION (SELECT col_1, col_2, ... FROM table_b MINUS SELECT col_1, col_2, ... FROM table_a);

Gombos Bertalan wrote:

> <snip>

> Okay. I know that it raises an exception when result is not a single

> row. My only goal is to compare two tables. I'm not intrested in any
> other result. Supposing A and B are the tables to compare, (A minus B)
> union (B minus A) is empty if tables equals. I would like to decide bye
> one select.
>
> Bye:
> --
> G o m b o s B e r t a l a n
> system engineer, Oracle developer
> mailto:bgombos_at_freemail.c3.hu

--

Jörn Fieg                                               WMD GmbH
to replay, delete >remove_this< from the adress         Hamburg / Germany