How to perform an Inner Join in SAP-BODS and Execute it via Console

[Former Member]

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.

 

Step 1-->

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’.



 

Step 2-->

On the extreme right of your screen where you can find a pallet, select the query transform then click and drag it.



Step 3-->

Provide a Naming Convention to the Query Transform. Right click on the query you can find an option rename.



Step 4-->

Rename the Query Transform as you desire.



Step 5-->

Now Map those couple of Source files to the Query Transform as shown.



Step 6-->

Double click on the Query transform then you will be entering into the Query Transform.



Step 7-->

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.

Step 8-->

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

Step 9-->

Now you can find the “FROM“ option in schema Remapping.

Step 10-->

Right click on ‘From’ option then “input schema”, ”Joins pairs” and “From Clause” will be appearing in Schema Remapping.

Step 11-->

Now we have to select both the tables in input schema which we wanted to join in ‘FROM’ option.

Step 12-->

Now move to ‘Join pairs’ below the Input Schema and select left table as per the requirement.

Step 13-->

I will be selecting orders table as the left table depending upon scenario.

Step 14-->

Now select the Right table as per the requirement.

Step 15-->

Select “Join Type” which you wanted to do.

Step 16-->

I will be selecting Inner join here as highlighted.

Step 17-->

Now we have write the join condition at “join condition” option as highlighted.

Step 18-->

Now click “Open smart editor” as shown in below Screen shot.

Step 19-->

Now ‘Smart editor window’ Dialogue box will be opened in Designer area.

Step 20-->

Now select the common columns from those tables and write the join condition then after click on OK.

Step 21-->

Now get back to Data flow, Drag the Template table into Designer area and Map query transform to the Template table as shown.

Step 22-->

Now click on the validation icon highlighted, So you can check for any errors in your whole process.

Step 23-->

Now click on the ‘Save all’ option it then saves all your process the click OK.

Step 24-->

Now we have to do job execution so go to “Data Service Management console” which I have highlighted click on that.

Step 25-->

Then the ‘Data Service Management Console’ window will be opened in a Web Page where we have to provide our Credentials.

Step 26-->

Select the Administrator which I have highlighted below.

Step 27-->

Now we have to expand the “Batch” option.

 

Step 28-->

Now we have to select our Repository.

Step 29-->

Here the selected Repository status opened in console and now we have to select “Batch job configuration” and click on that.

Step 30-->

Now we can see all our Batch Jobs opened in console and choose our job and click execute.

Step 31-->

So, job execution window is opened, select the require options and click on execute that is highlighted.

Step 32-->

Now job started successfully if we want see the job log status then click on” click here” option to view your Log.

Step 33-->

Finally if your whole process went well then you can see your Job got executed successfully.

Thank You.