Implementation of SQL JOINS in Boomi
This blog will cover how we can perform SQL JOINS in Boomi.
Prerequisites:
We have to create two tables in the database named Employee_Blog and Department_Blog having columns Id, Name, Dept ID and Dept ID, Dept name respectively.
Figure 1: Employee_Blog
Figure 2: Department_Blog
Step 1: Log on to the Boomi platform (https://platform.boomi.com/) with a username and password.
Step 2: Once, you have logged into the Boomi platform, you can view the Home page.
Step 3: Now, click on Services followed by Integration. You will see the Build page. Click on Create a new process.
Step 4: A New Process will open with a Start shape. Choose the Type ad No Data and click OK.
Step 5: Click on the plus sign and choose Database Connector.
Step 6: Click on the Configure below the Database Connector.
Step 7: Add Connection.
Step 8: Set the values for Driver Type, User Name, Password, Host, Port, and Database Name (Schema Name). Then click on Save & Close.
Step 9: Select the action as Get & Add Operation.
Step 10: Add Profile.
Step 11: Click on Statement. Click on Import.
Step 12: Select ‘Browse in’ as your run time engine. Select the connection (Step 8)and put the schema name in the Schema Filter.
Step 13: Select the required tables.
Step 14: Select the required columns from the table Department_Blog.
Step 15: Select the required columns from the table Employee_Blog.
Step 16: Write / Modify the query in the SQL Script section. Click on Save & Close.
SELECT Department_Blog.`Dept Name`, Employee_Blog.Id, Employee_Blog.Name, Employee_Blog.`Dept ID`
FROM Department_Blog JOIN Employee_Blog
ON Department_Blog.`Dept ID`=Employee_Blog.`Dept ID`
Step 17: Click on OK.
Step 18: Click on the ‘+’ and choose the Stop shape.
Step 19: Click on Save and hit Test.
Step 20: Choose the runtime engine.
Step 21: Click on the Stop shape and click on the shape source data to check what data the database connector produced.
