Multi-Table Query Source

Multi-Table Query Source provides the functionality to retrieve data from a data model or a virtual database. Additionally, it allows you to design Filters, specify Where Clause, and set Sort Order to control the result set.

Using Multi-Table Query Source

You can use a Multi-Table Query Source to fetch data from a data model or a virtual database. To call a data model, you will have to provide the path of the folder where the data model is saved. In case of a virtual database, you will have to provide the connection of the database where the virtual data model is deployed, and the root element which is the starting point for the mapping tree, Filter criteria, Where Clause, and Sort Order.

To add a Multi-Table Query Source in your integration flow, locate the element in the toolbox under the Sources group, and drag-and-drop it into your flow.

mceclip0.png

To configure a Multi-Table Query Source object, right-click on it and select Properties from the context menu. The following properties are available:

mceclip1.png

Data Model

Data Model is selected as the default Model Type. If you want to continue with it, then provide the Model Path by clicking on the Browse Files icon at the right side of the Model Path box.

mceclip2.png

If you want to provide a database connection different than the one used in the data model, then put a check on Override Database Connection in the Model below the Model Path.

mceclip3.png

Click on the dotted box to configure the database connection.

mceclip4.png

This will open the Database Connection window, where you can specify the database with which you want to override the connection.

mceclip5.png

Next, you have to select the Root element from the entities that you have in your data model. In the data model that we have specified in the screenshot, there are 2 entities: Products and Suppliers. Therefore, you can see both in the drop-down menu of Root. You can select either of these depending on which entity you want to see in the mapping tree and use it for Filter, Where Clause, and Sort Order.

mceclip6.png

Virtual Database

To use a virtual database in the Multi-Table Query Source, select Virtual Database in the Properties window.

mceclip7.png

Now, click on the dotted box to specify where the virtual data model is deployed.

mceclip8.png

You can either specify a recently used connection or a new one. Select Astera Data Model as the Data Provider, the Server on which the virtual data model is deployed, and finally the Virtual Database that you want to use in the Multi-Table Query Source. Once you have specified the connection, test it, and click Ok after the test is successful.

mceclip9.png

Now, select the Root element from the drop-down menu. In the screenshot below, the virtual data model ‘VDM_Test’ contains 2 entities that you can see in the drop-down of Root. For now, we will select ‘Orders’.

mceclip10.png

Now, click on Next to go to the Query Source Layout window. On the left, you can see the mapping tree, and on the right, you can apply Filter, write Where Clause and set Sort Order.

mceclip11.png

You can filter the records by clicking on Add a Filter to the Query icon.

mceclip12.png

Provide the Path of the field on which the filter will be applied. For this example, we have selected ‘EmployeeID’.

mceclip13.png

Now, provide the criteria based on which the filter will work. The available options are Equals to, less than and Equals to, and greater than and Equals to. Apart from these, the Other option is also available for the Filter criteria.

mceclip14.png

Let’s select equals to 5 for now. You can preview the output of Multi-Table Query Source to view the filtered record set.

mceclip15.png

In the Where Clause screen, you can write queries on any field in the table as shown in the screenshot below.

mceclip16.png

To sort the record set of a field, click on the Add a Field to Sort By to the Query icon in the Sort Order window.

mceclip17.png

Now, select a field in Path and apply the ascending or descending order in Sort Order. You can preview the output of Multi-Table Query Source to view the sorted data.

mceclip18.png

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.