Note on 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. What ever the SQl entered by you that will not be validated by DI.
Options: Mainly will discuss about 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. The beauty of this option is when you have multiple config’s then you can write SQL for each database.
SQL Text: Write your SQL query here.
Desgin Steps: Here i’m performing 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 wirte your SQL query window where the space provided. Then Press Update Schema to fetch the columns from backend.
- That’s it you’re done with the design, save the job, and execute it.