Hey guys my first post/questions and a hearty welcome to the community. My question is a howto type. I want to take the data from one query and feed it into another doing a diff to ensure the data is the same. I have been using the compare results feature but as my result set has grown to tens of thousands of rows, eyeball comparisons have become tedious.
My use case is as follows. I query a Netezza db, then I query an oracle db and compare the results for equality using results compare. I’d prefer to take the results from Netezza and use an in clause in the oracle query, but Oracle has that thorny 1000 element limitation. I want to JOIN the two queries but I don’t know how to do this against two totally different db’s where there is no DB link.
select O.* from (select O.values from Oracle O)
join (select N.values from Netezza N)
on N.id = O.id
Subquery gymanstics aside, is there a way to have ADS read from a csv file a parameterized list of values to feed into a query?
Is my question clear?
Thanks in advance
Response
Niels Gron over 10 years ago
There are two features which Aqua Data Studio doesn’t support that you are requesting above :
1) JOIN two tables from two different server connections
2) JOIN a table from 1 server connection to a CSV file
But there are two things to consider to make your comparisons eaiser :
1) ADS version 12.0 has made huge improvements in Data Compare, so make sure to use version 12.0 :
“we sped up query results by 10x, and resultset compare by 850x.”
2) If your datasets get bigger, you can increase memory capacity to handle the large results :
https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation12/page/50/Launcher-Memory-Configuration
3) If you are looking to automate your compares, what you can do is use FluidShell. You can write a script such as :
>> file : mycompare.fluid
disconnect
connect MySQL
select 1
union
select 2
union
select 3
sqlexport –column-names false > table1.txt
disconnect
sleep 2
connect MySQL
select 3
union
select 4
union
select 5
sqlexport –column-names false > table2.txt
compare table1.txt table2.txt
disconnect
In FluidShell terminal you execute the script with :
:$ source mycompare.fluid
If these items don’t help, let us know more about the scenario and the problems.