Re: check if the results of two query are the same or not

Good point - it's only valid where each
separate query returns a set of distinct rows.

I can't think of a good solution at present. I've thought of a very bad solution - which would work (I think) for a limited number of columns, and might be acceptable for a
relatively small amount of data.

This entails putting the two queries into inline views which ordering the rows by EVERY column and apply the rownumber() analytic function to every row. Then you join the two views on every column, including the rownumber()
and count the output. If the count() is the same as the number of rows in the original queries, then the two data sets are identical.

(But I did say it was a bad solution).

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



slim wrote in message <491f65ba.0205230707.4c00bf74_at_posting.google.com>...
>"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:<1022143464.25395.0.nnrp-01.9e984b29_at_news.demon.co.uk>...


>> various options but pure sql solution would be:

>>

>> select * from

>> (

>>     (q1 minus q2)  -- all rows from q1 not in q2

>>     union all

>>     (q2 minus q1) -- all rows from q2 not in q1

>> )

>
>  thanks for you, I have yet tried this solution but suppose that q1 return
>for example 3 rows :
>     col_value
>     ---------
>             7
>             7
>             6
>and q2 return also 3 rows :
>     col_value
>     ---------
>             7
>             6
>             6
>
>Your query should not returns rows but the result of q1 and q2 are not the
same
>