Today, I will discuss about the Microsoft Access input component in Pentaho. There are many times we as developers comes across the scenario where we need to write the Sql to join the multiple tables in Access db. So, we tend to use either table input or Sql component to achieve the same by creating database connection for Access db. I tried this multiple times but it didn’t worked for me.
Second way to handle the multiple table joins in Access db is to write the Sql query in Access db itself and you can convert that query into table. Fetch the data from this derived table using Microsoft Access input component. But what if you as a developer does not have permissions to change or create any table in Access db. Then above mentioned solution will not work as well.
Now, we will use Microsoft Access input component multiple times to achieve the same. See the below image.
Here, I have created two tables in Access. one is emp_details and another is emp_salary_Details. Below is the image for the same.
Now, use Microsoft Access input twice to get data from these tables. After that sort component is added to sort the data on emp_id. Eventually use merge join (use INNER JOIN ) on condition Emp_details.Emp_id=Emp_salary_Details.Emp_id. Hence we join the two tables in Access db using Microsoft Access input and Merge join component.
Added the Microsoft Access db file and ktr in git. Below is the path for the same.
https://github.com/Karan-Arora-13/technicalstuff