Implementing Query Transform Using Joins

Query Transform:

• Query transformation is the most commonly used transformation in BODS.
• Query transform is used to retrieve a data set based on the input schema that satisfies conditions that we specify. A query transform is similar to a SQL SELECT statement. The Query transform is used to perform the like operations Maps column from input Schema to output Schema, Perform transformations and functions on the source data, Assign Primary Keys to output Schema columns etc.

Joins:

• Join is a query based on a condition.

Steps :-

• Right click on Project -> New -> Data Flow.

• Provide Name and Description.

• Double click on ECC - > click on add objects by Name.

• Enter Table name and click on OK.

• Again click on add objects by name to add TSPAT table.

• Enter table name and click on OK.

• Click on save.

• Now we can see the tables under ECC Data store.

• Drag and drop TSPA to the Data Flow Editor.

• Drag and drop TSPAT to the Data Flow editor.

• Drag and drop Query Transform to the Data Flow Editor.

• Join TSPA to Query Transform.

• Join TSPAT to Query Transform.

• Add required input schema's to the Query Output schema.

• Click on Joins.

• Click on Add.

• Take left schema as TSPA and join type as Inner Join and Right schema as TSPAT.

Inner join:

•  The inner join captures the common data from both the tables based on common fields.

• Click on expression Bulb.

• Click on confirm and click on OK.

• Drag and drop Template table to Data Flow editor.

• Click on show list of values.

• Select Target system and click on OK.

• Provide Table owner and Table name.
• Click on OK.

• Join Query to Template Table.

• Click SAVE.

• Click on validate.

• Pop-up will appear "Validation result is successful".
• Click on close.

• Click on execute.

• Select job server and click finish.

• Job execution is success.

• Right click on Template Table -> Click on view data.

• Raw Data.

• Go to SAP-HANA studio -> Expand System -> Catalog -> Table Owner.

• Right click on Table -> Click on Filters.

• Enter Table Name and Click on OK.

• Right click on table ->  Click on Open Data Preview.

• Raw Data