Implementing Query Transform Using Filters

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.

Filters:

• Filters are expressions which limit the data that is retrieved.
• For instance you can restrict the data retrieved based on the date or SAP client using filter expressions.

Steps :-

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

• Provide Name and Description.
• Click on Finish.

• Double click on ECC data store.
• Click on add objects by Name.

• Enter table name and click OK.

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

• Enter table name and click 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 to the Data Flow Editor.

• Join TSPA Table to Query Transform.

• Join TSPAT to Query Transform.

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

• Click on Where

• Click on Edit filter

• Drag and drop SPARS and apply Filter .
• Click OK.

• Click on Joins.

• Click on Add.

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

• 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 OK.

• Provide Table owner and Table name.
• Click 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 on Finish.

• Job execution is success.

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

• Raw Data.

• Go to SAP-HANA studio.
• Expand System.
• Expand Catalog. 

• Right click on Table -> Click on Filters.

• Enter Table Name and Click OK.

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

• Raw Data.