Note: this information refers to Jet Data Manager version 2017 and higher. For older versions of the JDM, see How to Use SQL Snippets
In the Jet Data Manager you can create commonly used pieces of SQL code and parameterize them as SQL Snippets. This allows frequently used pieces of functionality to be saved once and then easily deployed across different tables or fields without recreating all of the SQL functionality.
SQL Snippets can be used with:
- Field level transformations
- Stored procedures
- User defined functions
- Script actions
On the Tools menu, click the Snippets button.
From Snippets window, you can Add, Edit, Organize, Import, and Export all snippets regardless of the type. To further streamline the snippets feature business functions have been renamed OLAP snippets.
How to Create a SQL Snippet
1. On the Snippets windows, click the Add button
A window with the following options will appear:
2. Type and Name and Description for the SQL Snippet. The Description is option but allows other users know what the Snippet will do.
3. Type or paste the SQL command into the Formula section.
4. For any variables (in this example FieldName) highlight the variable and click Add Parameter. This will add the highlighted text as a parameter name in the Parameters section.
5. Change the Type to match what the variable represents. The available options are: Table, Field, Database, User Defined Function, Stored Procedure, and Value.
6. Click Ok to save the SQL Snippet.
How to Edit a Snippet
1. On the Snippets window, select the snippet you wish to edit and then click the Edit button.
3. Adjust the Snippet as needed.
4. Click Ok to save the SQL Snippet.
How to Delete a SQL Snippet
1. On the Snippets windows, select the snippet you want to delete and then click the Delete button:
2. Click Yes to permanently delete the SQL Snippet(s)
How to Implement a SQL Snippet in a Project
1. Right click on the field to add the SQL Snippet to and go to Add SQL Snippet Transformation and select the desired SQL Snippet from the available list.
2. Drag the desired field(s) from the Data Fields pane on the right and drop the field on the Object Name/Value column for the desired variable. The Object Name/Value column and Variant column will populate automatically.
3. Click Ok.