SQL Transform in Data Integrator

SQL Transform: You can write your own customized SQL query here. It appears in your dataflow as a source. Use this transform when you cannot perform using other DI transformations. Whatever the SQL entered, it will not be validated by DI.

Options: Mainly there are three arguments here

Datastore : Select the datastore to access the tables that you’re referring in your SQL query

Database Type: When you have multiple configurations then you need to select the database type and version which you want, else default database will be selected automatically.

SQL Text: Write your SQL query here.

Design Steps: Below is an example of FULL OUTER JOIN using SQL Transform

Follow the basic steps like drag the SQL Transform from tranforms object library, query transform, and finally place a template table for target. Now, connect each object-

  • Now click on the SQL Transform and write your SQL query in window where the space is provided. Then Press Update Schema to fetch the columns from backend.

  • Now, you’re done with the design, save the job, and execute it.