SQL & PL/SQL

Hi Hartford,

Query two views using a subquery with exists or not exists operator.

Regards,
Rodrigo Mufalani

Any particular reason for using subquery with exists or not exists operator.

I'd do it the old fashioned way, but someone will probably point out something fancier. If it is a repeating task I'd resort to SQL of course.

Spool two select statements to a file, one for each view, and select only the common columns and order by the columns that are the "primary key". After that you do a file comparison.

Regards,
Rob.

Try something like this -

select count(*)
from v1;

select count(*)
from v2;

select count(*)
from (
          select *
          from v1
          intersect
          select *
          from v2
       );

If all the query returns same count, then there is no data difference among those views.

This solution only applicable if both views have same structure.

Regards.

Satyaki De.

Lazy way, use a count(*) on both =). They should be equal.

If besides checking on number of rows want to review possible data differences

select col1...coln
from view1
minus
select col1...coln -- except for the new column added
from view2

The resulting query will be the difference

What will the resulting query be for
select col1...coln
from view1
minus
select col1...coln -- except for the new column added
from view2.

Bad wording, the query results could be:

No rows selected. Same number of rows on both views and same data (considering view1 structure and view without the new column)
At least one row selected. If the count(*) on both was different, rows returned will be at least the difference. If the count was the same, then every returned row means that there's no equivalent row on view2 and a data difference may exist in at least one of the fields, so You have to find the equivalent row on view2 to compare.

On the query sintaxis, as view2 hava a new column and I guess it's not a constant value, You have to specify every column for the view1, and the same columns for view2, so new field isn't included and compared.

Hi Hartford,

If tables has primary keys, one way to compare both is using EXISTS operator like this:

1. Identify rows that exist only in tab1

select
a.col_id -- your primary key
from
tab1 a
where
not exists (select
1
from
tab2 b
where
a.col_id = b.col_id);

1. Identify rows that exist only in tab2

select
a.col_id -- your primary key
from
tab2 b
where
not exists (select
1
from
tab1 a
where
a.col_id = b.col_id);
Regards,
Rodrigo Mufalani