The Results Compare Tool provides a user with graphical representation depicting differences between two result sets obtained from either the same query or two different queries.
Contents
Launch the Results Compare Tool
To launch the Results Compare Tool, first, execute queries, and then in the Aqua Data Studio menu bar, click Tools > Compare Tools > Results Compare. The Select Result Sets dialog displays a list of result sets of the executed queries, out of these, you can select only two for comparison. The Select PK tab in the dialog displays the common column headers present in both the selected result sets. The compare tool can filter the records in the result sets based on the applied primary keys. For more information, see Primary Key.
Comparison Results Scenarios
This section lists the scenarios based on which Results Compare displays the result.
Scenario 1: Result Sets Contain All Matching Columns
When both result sets contain same columns, then Results Compare shows records of the result set 1 followed by the records of the result set 2. You can scroll vertically to view the complete result. All common column names are displayed and selected by default in the Primary Key tab.
Scenario 2: Result Sets Contain Some Matching Columns
When both result sets contain a combination of the same and different columns, then Results Compare shows the result in this order:
- Columns present only in the result set 1
- Columns common for both the result set 1 and the result set 2
- Columns present only in the result set 2
You can scroll horizontally to view the comparison result. Columns present in one result set but absent from the other, Results Compare show their cell values as Null. All common column names are displayed and selected by default in the Primary Key tab.
Scenario 3: Result Sets Contain All Different Columns
When both result sets contain all different columns, the Results Compare shows only the first row from both the result set. You can scroll horizontally to view the comparison result. No column names will be displayed in the Primary Key tab.
Comparison Results
The Results Compare shows the comparison result as per the listed scenarios.
The result sets you have selected in the Select Result Sets dialog are displayed together according to their state. This table describes the color code and results for each state.
State | Description |
---|---|
The records exist in result set 1 but not in result set 2. | |
The records exist in both result sets but are different. | |
The records exist in result set 2 but not in result set 1. | |
The records in both result sets are equal. |
You can use the navigation toggle keys (, ) to navigate between different states and within the same state.
On clicking a cell, view its content in the Preview Panel, also known as the Selected Cell Content area. Contents in the deleted state are displayed in the upper section. Contents in the added state are displayed in the lower section. Contents in the equal and changed states are displayed in both the upper and the lower sections. You can show or hide the Preview Panel by clicking on the toolbar.
You can see the executed query details in the Results Compare for header. You can show/hide this header by clicking on the toolbar. You can also see the total of fetched records and the total time taken to fetch these records right below the Grid tab. You can show/hide this information by right-clicking anywhere in the grid, and then selecting Show/Hide Toolbar.
Results Compare also supports repositioning of columns and multicolumn sorting with ascending and descending. For example, to sort Country in ascending order and Sport in descending order, click the Country header and then press Ctrl, and then double-click Sport header. The icons appearing on the headers are a sign of successful sorting.
Other than this, you can filter comparison result using primary keys or filtering, perform a search operation, and change the display of the grid result using . You can assign new colors to states from the Options dialog. You can refresh the comparison result or perform a new compare. You can save the comparison result in HTML and export it to Excel.
Refresh
Click to refresh the comparison result. For backend changes, first, re-execute the queries, and then perform the refresh operation by selecting the same result sets from the dialog.
If you have executed multiple queries, then you can also choose a different result set via Refresh. In the Select Result Sets dialog, choose a different result set for comparison. Results Compare displays the comparison result in the same Results Compare tab.
You can also perform a new compare when you right-click anywhere in the grid view and choose Results Compare.
Filter
Click , to view the comparison result as per state. When you click , the Results Compare Filter dialog is displayed.
You can perform these actions in the Results Compare Filter dialog:
State | Action |
Added Rows | Click to show added rows present only in the result set 1. |
Deleted Rows | Click to show deleted rows present only in the result set 2. |
Modified Rows | Click to show modified rows in both result sets. |
Both Equal | Click to show equal rows in both result sets. |
Select all states (), removal (), and inversion () of the last action. |
When you apply a filter, Results Compare shows the Enable filter check box as selected, which means, a filter has been applied. If you unselect this check box, then Results Compare removes the applied filter from the comparison result.
New Compare
You can choose different result sets for compare by right-clicking anywhere in the grid view and then choosing the Results Compare option. Results Compare displays the comparison result in a new tab.
Search
Quick filters offer the advantage of filtering the records displayed in the grid view, based on the user-specific value, and finding information quickly. By using the Search box, you can define a scope to:
- Search for text and numbers
- Perform a case sensitive or case insensitive search
- Search by using wildcards and regular expressions and match found options
Navigating in the Grid
You can navigate between different states and within the same state by using the and toggle keys.
Right-Click Actions
The table describes the right-click actions in the grid view.
Right-Click Action | Description |
Select All (CTRL/Command+A) | Selects all records in the grid view. |
Results Compare (CTRL/Command+Shift+K) | Opens the Select Results Sets dialog. |
Copy (CTRL/Command+C) | Copies the selected records in the grid view. |
Copy... | Shows additional options when you point to this option. |
Copy As.. | Opens the Copy to Clipboard dialog. |
Paste to Window | This option is disabled and not applicable to Results Compare. |
View As Text | Displays the selected record in the View dialog. |
Show/Hide Toolbar | Shows/Hides the toolbar in the grid view. |
Copy...
Copy is a right-click option and shows additional options when you point to this option. The following table lists its additional options.
Option | Description |
Copy With Headers | By default, Results Compare copies data without the column headers. This option copies the headers, along with the data. See Attachment. |
Copy with Row Count | Copies the row count, along with the data. See Attachment. |
Copy with Headers and Row Count | Copies column headers and row counts, along with data. See Attachment. |
Copy Headers Only | Copies only column headers. See Attachment. |
Copy All with SQL | Copies the row count and column headers, along with the query from which the result set is obtained. |
Copy As
Copy As is a right-click option and opens the Copy to Clipboard dialog. The following table lists the options in the Copy to Clipboard dialog.
Option | Description |
---|---|
Data Order | Retains the data presentation for horizontal data order. In Vertical data order, displays columns as rows and rows as columns. |
Separator | Adds a separator between 2 cell values. |
Quote Identifier | Adds quotes across cell value. |
Leading Enclosure | Inserts user-specified characters at the start of copied data. |
Trailing Enclosure | Insert user-specified characters at the end of copied data. |
One line per table row | Inserts complete data in one line. |
Wrap Line at Column | Wraps line at a user-specified value and then displays data in the next row. |
Save and Export Comparison Result
Save as HTML
You can save the comparison result as HTML for viewing later or for sharing with others. Click (Save As HTML) and provide the file destination and font selection. You can choose whether to open the exported file immediately after the export process completes. If you click (Preview in browser) without saving the file then you are prompted to save the file before launching a web browser. If the comparison has already been saved, then Results Compare displays the result in a web browser.
Export to Excel
Click and view the result in the Excel file. This file is saved in the Temp folder of your system.
Primary Key
Primary Keys tell Aqua Data Studio against which rows to perform a match. When you compare result sets, the common column headers in both result sets are primary keys. In the following example, after applying primary keys on the Total Medals column, Results Compare matched the unique records for Total Medals and displayed the contents of the matched records as identical or not. Also, see Comparison Results Scenarios.
Limitations: If a query returns multiple rows with the same primary keys, then there is no reliable way to identify modified rows in the result sets. Multiple rows of the same primary keys are obtained in 2 cases: due to user selection of the primary keys and the selection of the query columns. Initially, Results Compare is run with all of the common columns selected as primary keys. Here, if the query returns duplicate rows, it is due to query column selection. If a table has duplicate rows and no primary key defined, then there is no way to determine if those rows have been modified even though a user selects all of the table columns for the query and all of the common columns as primary keys. If this problem occurs, Results Compare is still run on the result sets using intelligent assumptions about what rows correspond to each other, but a user is warned about duplicate rows. If there are duplicate rows in the result sets on the initial run, then a user is advised to change column selection in the query. If there are duplicate rows obtained after a user changes the set of primary keys, then the user is advised to redefine the primary keys.