SQL Transformation in SAP BODS
SQL transformation is used to perform standard SQL Operations in Data services but it is not supports all SQL operations. It supports only retrieval operations (select statements).Using SQL transformation we can’t manipulates (Insert, Delete, Update) the data.
SQL transformation and lookup function have done same operations. .. Performance of SQL transformation is very good compared to lookup function.
- SQL transformation supports only tables not files or other sources in SQL text.
- In this scenario, we have three source tables as shown in figure 1, 2 and 3 but we want 3 source tables’ data information in one target table based on business requirement.
- In this condition we go for SQL transformation. SQL transformation is used to join the 3 source tables and gets the output in single target as shown in figure 4.
- NOTE: – You can get same output using lookup function but it will take more time compared to sql transformation
Figures 5, 6 and 7 shows the object hierarchy for validation transformation job, ETL job flow and the way we define the SQL statements respectively.
In SQL Transformation:-
- The name of the Data Store that Data Services uses to access the tables referred to in SQL text.
- In SQLtext, select statement is used to get the data from three tables using join condition.
- Click the option updateschema to populate the output schema for the SQL SELECT statement.
- You can these options in below figure 7.