Working with Reusable Query Snippets

Versions: 8.0+

Working with QuerySurge Reusable Query Snippets

A QuerySurge Reusable Query Snippet is pretty much what its name says it is – a Query snippet or fragment that can be reused in multiple queries. A Query Snippet does not have to be a syntactically intact Query (for example, it might just be a where clause) but there is no reason why it can’t be a complete Query (e.g. a reusable subquery).

The Query Snippet feature of QuerySurge lets you develop libraries of reusable Query snippets, so that if a “snippeted” portion share by multiple queries needs to change, you can make the change once in the Snippet library and the queries using the Snippet are fixed automatically.

Creating Reusable Query Snippets

You can input your reusable Query Snippets into the Snippet editor with a Snippet name. After you have inserted the text, you must save the Snippet. For Snippet-related sizes, see Sizes of QuerySurge Things.

Reusable Query Snippet Creation

Snippets must be created before they are used in queries. If you try to enter a snippet name in a query that doesn’t exist, QuerySurge should advise you of this and will ask you to save the Snippet before you save the query using it.

Note: Reusable Snippet names must be unique. QuerySurge should indicate to you when a name is not unique, and ask you to use another name.

You can create Snippets either by typing them directly in a new Snippet in QuerySurge, or by highlighting text in a QueryPair Query and using the 'Save select SQL as Reusable Query Snippet' tool in the Query Editor toolbar:

This tool lets you create a Snippet containing the highlighted text in the Query Snippets folder in the tree, and a Snippet reference will be inserted into the Query from which you created it.

Reusable Query Snippet Names

Snippet names must follow some simple rules:

1. Snippet names must start with a letter
2. Snippet names may contain:
   1. both lower- and upper-case letters
   2. the digits 0 - 9
   3. the following punctuation symbols:
      • dash (-), underscore (_), period (.), parentheses (), comma (,) and space.
3. No other characters should be used.

Snippet References

QuerySurge indicates a Snippet reference using the following syntax: ${...}, where the contents of the Snippet are between the curly braces. This sequence is reserved by QuerySurge, along with some other syntactic rules:

1. Snippets may not be used inside single quotes – the quotes need to be part of the Snippet.
   For example: select * from table t where t.text = ′${my snippet}′ (where ${my snippet} = why a duck?) is not legal.
However: select * from table t where t.text = ${my snippet} where ${my snippet} = ′why a duck?′ is legal.
2. If column names appear in a Snippet, the same syntax restrictions as are listed in previous notes apply (no use of $$’, no use of ${...}, and no commas in column names).
3. A Snippet, once embedded in a Query, is highlighted:
   
4. Note that the QueryPair Editor has both an Editor and a Query View for each Query in a QueryPair (see the tabs at the bottom of each editor). The Editor shows the full Query text with Snippets shown as Snippet references. The Query is editable in the Editor tab, as the name suggests. The Query View shows the Query with all Snippets resolved, so that you can see your intact Query. The Query View is not editable.

Note: You can see the full description of reserved syntax in About QueryPairs.

Using Reusable Query Snippets in QueryPairs

Once you have created a Reusable Query Snippet, you can use it in a Query. In the Query Editor, put the insertion point at the point where you want to insert the Snippet. Click on the icon to open the Add a Reusable Query Snippet dialog. Select the Snippet name you want at the left, and the snippet text will appear in the dialog. When you find the Snippet you want, click the OK button.

The Snippet reference will automatically be entered into your Query.

Note: The Snippet does not appear inline in the Query Editor – it appears by its name, in curly braces in bold font (e.g., “{source_lastFirstName}”) – as a Snippet reference. If you want to see the full Query inline, switch over to the Query View, either by clicking on the icon in the toolbar at the top of the editor (4th icon from the left), or by clicking on the Query View tab at the bottom of the editor. To return to the editor, click on the icon (3rd icon from the left) or click on the Editor tab. The purpose of the Query View is to allow you to see the full Query inline with all Snippets resolved, but you cannot edit the Query in this view. You can view the full Query in the Query View and you can copy it to the clipboard. Editing can only be done in the Editor.

Viewing Snippets

In addition to viewing the Snippet text either in the Query View or in a tooltip when you mouseover the Snippet reference, you can right-click on the Snippet and select view. This will open the Snippet in the Snippet folder. The Snippet can be edited as usual if desired.

Unlinking Snippets

In some circumstances, a Snippet reference must be unlinked from its source and resolved in a Query. This can be done with right-click on the Snippet followed by selection of unlink. Unlinking a Snippet leaves the Query with no Snippet, but with the full text including that which was originally part of the Snippet.

Deleting a Snippet reference in a Query

If you right-click on a Snippet in a Query and choose delete, the Snippet reference is deleted from the Query. The underlying Snippet is unaffected; only the local Query is affected. Deleting a Snippet may leave you with a broken Query, so don’t forget to fix the text of any Query from which you have deleted a Snippet.

Deleting Reusable Query Snippets

You can delete Snippets in much the same way as you can delete Queries in QuerySurge. Queries that use the deleted Snippet are handled as follows. On Snippet deletion, the Snippet text is resolved for all Queries that use the Snippet, and the text is inserted into the Query. The result is that the Query will contain the text of the deleted Snippet inline, so that you have an intact Query left after the Snippet deletion.