Description
I am looking for a simple oracle SQL query to compare results of two
select statements, and write result to text file. (I am open for any
other simpler solution instead of writing to text file. I don't have
Admin access, but can create a new table if required).

I will pass array of two ids expected_id and current_id
example :
expected_id[] = { exp1, exp2, exp3 }
current_id[] = { curr_id1, curr_id2, curr_id3 }


Compare two select statements like
example :
select * from out_pv where job_run_id=exp1
select * from out_pv where job_run_id=curr_id1

select * from out_pv where job_run_id=exp2
select * from out_pv where job_run_id=curr_id2

select * from out_pv where job_run_id=exp3
select * from out_pv where job_run_id=curr_id3


row by row and values col by col :

If count is not same - print - Status = "Failed" - remarks = "count is
not same
If count is same
Compare two select statements row by row and values col by col and if
all are same
Print - Status = "Passed" -remakrs = "Test passed"
If values are not same - keep comparing till end
print Status= "Test Failed - remarks "expected row # and curr row #:
exp value comma curr value"

On Sat, 27 Nov 2010 07:29:28 -0800, Mubashir wrote:

> row by row and values col by col :
> 
> If count is not same - print - Status = "Failed" - remarks = "count is
> not same
> If count is same
> Compare two select statements row by row and values col by col and if
> all are same
> Print - Status = "Passed" -remakrs = "Test passed" If values are not
> same - keep comparing till end print Status= "Test Failed - remarks
> "expected row # and curr row #: exp value comma curr value"

This looks like a procedural recipe, I would program this in Perl or some 
other scripting language. SQL is not a really good choice for procedural 
problems.



-- 
http://mgogala.freehostia.com