How to perform Left outer join in BODS and execute via console
The Main agenda of creating this blog i will be showing you how to do an Left outer join in SAP-BODS.
- 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 ‘CUSTOMERS’ and “ORDERS’.
- On the extreme of your screen where you can find a pallet, select the query transform then click and drag it.
- Provide the naming convention to the query Transform. Right click on that query you can find an option rename.
- Rename 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 column’s from ‘schema in’ to ‘schema out’
Note : we have to do mapping common columns from both tables on which we apply join condition
- The common columns must be mapped as per the below mentioned screen shots for easy recognition.
Note 1: Customers table common column mapped as per the picture.
Note 2: Orders table common column mapped as per the picture.
- Now can find the ‘FROM’ option in schema remapping.
- Right click on the “FROM” option then ‘Input schema’, ‘Join pairs’ and ‘From clause’ will be appearing in Schema mapping.
- 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 tables as per the requirement.
- I will be selecting customers table as the left table depending upon scenarios.
- Now select the right side table as per the requirement
- Select ‘Join type’ which you want to do.
- I will be selecting Left outer join here as Highlighted.
- Now we have write the join condition at “Join condition” option as highlighted.
- Now click “open smart editor” as show in below screen shot.
- Now Smart editor window” dialog box will be opened in designer area.
- Now select the common columns from those tables and write the join condition then after click OK .
- Now get back to Data flow, Drag the template table into designer 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 save 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 webpage where we have to provide our credentials.
- Select the administer 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 execute
- So, Job execution window is open, select the require option and click on execute that is on highlighted .
- Now job started successfully if you want see the job log status then click on ‘Click here’ option to view your log.
- Finally if your hole process went well then you can see your job got executed successfully.
Very useful, keep sharing