SQL snippets are small pieces of SQL code that can be used and reused throughout Prepare instances to speed up development. SQL Snippets can be used when applying Field level transformations. They can also be applied in Views, Stored procedures, User Defined functions, as well as Script Actions.

Add a SQL Snippet

1. Under Tools, select Snippets

   

2. Click Add and then select SQL Snippet

   

3. Provide a Name, Description and Formula for the snippet. Highlight the parameter in the formula and click Add Parameter

   

4. Under Parameters, change the Type to match what the parameter represents. You have the following options:
   • Table
   • Field
   • Database
   • User Defined Function
   • Stored Procedure
   • Value
5. Click OK

Using a SQL Snippet

Apply the Snippet using one of the following methods which may depending on where or how the snippet is being used.

To use a SQL snippet in a field transformation.

• Expand the Scripts folder
• Right-click the field.
• Select Add SQL Snippet Transformation. 
• Select the snippet from the list that appears on the right.

To use a SQL snippet as a stored procedure

• Expand the Scripts folder.
• Right-click Stored Procedures, and then select Add Snippet Stored Procedure.
• Select the snippet from the list that appears on the right. 

  

To use a SQL snippet as a user defined function:

• Expand the Scripts folder.
• Right-click the User Defined Function folder and then select Add Snippet User Defined Function.
• Select the snippet from the list that appears on the right. 

To use a SQL snippet as a custom step:

• Expand the Scripts folder.
• Right-click the Script Actions folder and then select Add Snippet Custom Step.
• Select the snippet from the list that appears on the right. 

To use a SQL snippet as a view:

• Right-click the Views folder and then select Add Snippet View.
• Select the snippet from the list that appears on the right. 

1. Map the available fields to the parameters in the snippet. Drag the field(s) from the list on the right and drop the field on the Object Name/Value column for the relevant variable. The Object Name/Value column and Variant column will populate automatically

   

2. Click OK

Edit or Delete a Snippet

 Under Tools, select Snippets. Select the snippet and click Edit or Delete

Show Snippet Usage

Under Tools, select Snippets. Select the snippet and click Show Usage

Troubleshooting

If the Snippet is returning NULL values, for example in a custom transformation, and the table where the snippet is being applied is an incremental table, perform a full load to ensure that values are updated for the snippet.

Example Snippets

Attached to this post you will find some SQL snippet examples. Please note that they are provided as-is and with no guarantee that they will receive updates or work in all future versions of the software. Please be aware that it is the content of the individual SQL snippet that determines its suitable usage.

In the attached XML file you will find the following snippets:

Date and Time:

• Current date - First date of future year
• Current date - First date of last year
• Current date - First date of month
• Current date - First date of previous year
• Current date - First date of year
• Current date - Last date of month
• DateDiff (variable end date)
• DateDiff (fixed end date)
• DateKey: Converts a datetime formatted field into an integer field for use as a better performing foreign key.
• First date of future year
• First date of last year
• First date of month
• First date of next year
• First date of previous year
• First date of year
• Last date of month
• Time Table: Script action that populates a custom time dimension with 15 minute increments 

Concatenate:

• Concatenate 2 Fields: Concatenates two fields separated by a space.
• Concatenate 3 Fields: Concatenates three fields separated by a space.