How to perform an Inner Join in SAP-BODS and Execute it via Console
The main purpose of creating this blog is i will be showing you how to do an Inner Join in SAP-BODS.
Its working point:
The inner join captures the common data from both the tables based on common fields.
Here on bottom left of the screen you can find the tables which you wanted to join. Select your desired tables and drag them. In this case my tables are ‘CUSTOMERS’ and ‘ORDERS’.
On the extreme right of your screen where you can find a pallet, select the query transform then click and drag it.
Provide a Naming Convention to the Query Transform. Right click on the query you can find an option rename.
Rename the Query Transform as you desire.
Now Map those couple of Source files to the Query Transform as shown.
Double click on the Query transform then you will be entering into the Query Transform.
Now we have to do the mapping to the required columns from ‘schema In‘ to ‘schema Out’
Note: we have to map the common columns from both tables on which we apply join condition.
The Common Columns must be mapped as per below mentioned Screen shots for easy recognition.
Note 1: Orders Table common column mapped as per the picture.
Note 2:Customers Table common column Mapped as per the picture
Now you can find the “FROM“ option in schema Remapping.
Right click on ‘From’ option then “input schema”, ”Joins pairs” and “From Clause” will be appearing in Schema Remapping.
Now we have to select both the tables in input schema which we wanted to join in ‘FROM’ option.
Now move to ‘Join pairs’ below the Input Schema and select left table as per the requirement.
I will be selecting orders table as the left table depending upon scenario.
Now select the Right table as per the requirement.
Select “Join Type” which you wanted to do.
I will be selecting Inner join here as highlighted.
Now we have write the join condition at “join condition” option as highlighted.
Now click “Open smart editor” as shown in below Screen shot.
Now ‘Smart editor window’ Dialogue box will be opened in Designer area.
Now select the common columns from those tables and write the join condition then after click on OK.
Now get back to Data flow, Drag the Template table into Designer area and Map query transform to the Template table as shown.
Now click on the validation icon highlighted, So you can check for any errors in your whole process.
Now click on the ‘Save all’ option it then saves all your process the click OK.
Now we have to do job execution so go to “Data Service Management console” which I have highlighted click on that.
Then the ‘Data Service Management Console’ window will be opened in a Web Page where we have to provide our Credentials.
Select the Administrator which I have highlighted below.
Now we have to expand the “Batch” option.
Now we have to select our Repository.
Here the selected Repository status opened in console and now we have to select “Batch job configuration” and click on that.
Now we can see all our Batch Jobs opened in console and choose our job and click execute.
So, job execution window is opened, select the require options and click on execute that is highlighted.
Now job started successfully if we want see the job log status then click on” click here” option to view your Log.
Finally if your whole process went well then you can see your Job got executed successfully.