User defined SQL functions in Jitterbit App Builder

User SQL function syntax can be placed inline with App Builder supported syntax for parameter substitution.

Example user defined SQL function

For the purpose of illustration, we'll describe a use case for having a User Defined SQL Function:

• You have two tables: Employees (Original table) and EmployeesPanel (New table)
• Both tables have same keys
• One table contains values loaded from the data source, the other was used as a panel source

If with the above scenario you want to display the Original value or Original value !! '-------' !! New value for a particular field, you can do so with a User Defined SQL Function. In this example, we'll use a User Defined SQL Function to compare the FirstName value from our Original table (Employees, alias as E) and the New table (EmployeesPanel, alias as EP).

Here's the definition for our example User Defined SQL Function:

IIF({0} = {1}, {0} , {0}  || '------>' || {1})

Here is a screenshot illustrating how this example scenario using a User Defined SQL Function might look from the App Builder App view:

To create a user defined SQL function

User Defined SQL Functions get created in the App Builder IDE Configurations area, on the Functions screen.

1. Go to the Action Drawer and select App Builder IDE from the menu
2. Select Configure your site from the Configure menu
3. Click on Functions
4. Click the Create button to add in a new User Defined SQL Function.

5. Provide information about the User Defined SQL Function:

   • Owner - Defaults to value
   • Type - MVSQL or SQL
   • Name - User defined name for function, should start with an underscore character. For example, _DiffFirstName
   • Number of Arguments - Number of arguments in the function
   • Definition - The actual SQL syntax for the function
   • Description - Text description of what the SQL function does
   • Example - An example using the SQL function
   • Example Results - Results from the SQL function

   

6. Click Save

To use a user defined SQL function in a rule

After you have created a User Defined SQL Function, you are ready to use it in a Business Object Rule.

1. Go to the Action Drawer and select App Builder IDE from the menu
2. Click Build your application from the Create menu
3. Click on the Business Logic Layer menu button
4. Click on the BOBJ icon for your application data source
5. Click Create under Rules
6. Assign a Name for the User Defined SQL Function, and select Business Object as the Purpose
7. Click Save

8. Click the SQL icon to define the SQL logic for the User Defined SQL Function. The steps to configure this function are similar to how you work with defining other SQL functions in App Builder. When you get to the Columns panel, you will reference your User Defined SQL Function Name along with the syntax:

   

9. If you want to use the Column value from the User Defined SQL Function elsewhere in the application, click Create under Columns and add in a Unique ID that will tie the Tables together.

10. In this example, we want to see the results of the User Defined SQL Function on the Employees Panel which uses EmployeesPanel (Source). Go to the EmployeesPanel (Source) Business Logic and click on the SQL icon for the EmployeesPanel (Source) Business Object.
11. Click Create under Tables and bring in the User Defined SQL Function Business Object Rule. In this example, it is Employees (UDF _DiffFirstName).
12. Create an Inner Join on the Table and Rule
13. Join the Columns on the Unique ID
14. Click Create under Columns to add in a Column with the resultant value from your User Defined SQL Function that you want to see. Assign an Alias and click the checkmark to save.