Setting up Shared Key Columns in QuerySurge

Versions: 8.0+

Versions:2.5+

Shared Keys on QueryPairs

QuerySurge has a number of QueryPair configuration choices that users can set that affect the data analysis process, and one of the most important is to set a Shared Key between the Source and Target queries in your QueryPairs. The reason that this is important is because the use of Shared Keys lets QuerySurge give you the most complete insight into the comparison of your Source and Target resultsets.

With a Shared Key set, QuerySurge can determine affirmatively the row match-up between Source and Target resultset rows. Otherwise, QuerySurge has to make assumptions in the analysis of the data, and in some cases, these assumptions may not hold. A full description of how QuerySurge analyzes data, including a discussion of Shared Keys, can be found in our "Key Column Specification" Knowledge Base article. The current article describes below how to set up Shared Keys on your QueryPairs.

Shared Keys

As with all QueryPair Properties, Shared Keys are defined on the Properties tab of the QueryPair, in the Shared Key Column Definition section:

Generate Column Names from the QueryPair

Once you open the Shared Key Column Definition on the Properties tab, you can start the definition process by choosing to base your key definition either on the Source or Target query, by choosing an origin for the key. Because QuerySurge requires that Source and Target queries must have the same number of columns in identical logical order, the key only needs to be defined on one side. In the example below, we use the Source Query:

Once the Key Column Origin is selected, the key itself can be defined. In order to define the key, the column or columns involved need to be indicated. The  button auto-populates all available column names based on your query:

Once your column names are populated, you can select the column or columns that constitute the key. Remember that these columns should form a key that is shared between the Source and the Target resultsets.

Note: A Shared Key must be both Shared and a Key. That is: the values must be shared between Source and Target, meaning they must be identical between Source and Target resultsets, and they must be keys, meaning that they must be unique in each row of each resultset.

Note: You can select a maximum of 16 key columns. 

Add Column Names Manually

There are some cases in which QuerySurge may not be able to parse your query to auto-populate the column names. You can add in column names manually by clicking on the  button and typing the column name. Column names are returned with your resultset, and it may be helpful to add aliases to your query:

Deleting Column Names

Column names can be deleted individually by clicking on the  button in each row.

You also can clear all column names by clicking on the  button:

Once you've set up your Shared Key Columns, don't forget to save the values with the Save button in the lower right of the main panel.

As we noted in the introduction, you can find a full description of how QuerySurge analyzes and displays data, including a discussion of Shared Keys, can be found in our "Key Column Specification" Knowledge Base article.

Note: It is important to be aware that there can be unintended consequences if you use other QueryPair Properties in combination with the Shared Key option. An example would be using the Data Type Checking Off option (see the QueryPair Properties tab) with a Shared Key column set. This is because when Data Type Checking is set to 'Off', all data is viewed as string data, and a Shared Key column when viewed as a string may be converted by QuerySurge to a CLOB data type. This could cause the Key calculation to abort. Therefore, if Shared Keys are used, the Data Type Checking On option should always be used. QuerySurge does not prevent you from using this combination of Property settings, because it may be helpful in specific cases, but it should be used with caution.