Note: this information refers to Jet Data Manager version 2016 and earlier. For current versions of the JDM, see How to Use SQL and Business Function (OLAP) 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
How to Create a SQL Snippet
1. On the Tools menu, click the Create SQL Snippet 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 SQL Snippet
1. On the Tools menu, select Edit SQL Snippet.
2. Select the SQL Snippet to be edited from the list and click Ok.
3. Adjust the SQL Snippet as needed.
4. Click Ok to save the SQL Snippet.
How to Delete a SQL Snippet
1. On the Tools menu, select Delete SQL Snippet
2. Check the box next to the SQL Snippet(s) to be deleted. Alternatively, Select All can be checked to automatically select all SQL Snippets.
3. Click Ok.
4. 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.