Goal
This document describes the query delegation behavior of an obligatory (OBL) field used in the WHERE condition of a query fired against a Web Service data source.
Content
Some of the data sources such as Web Services may allow only limited query capabilities on its data. In those cases, one can define the query capabilities on the Search Methods tab of the base view.
In these kind of scenarios, when a field of the base view is defined as obligatory with the allowed operator as “=”, and the “IN” operator is used in the WHERE condition of the query, the execution engine of the Denodo will convert the query by using the “=” operator in order to make it compatible to the specified Search Methods and will delegate one request for each value given in the IN clause.
Once the output is received from the data source, Denodo will combine them in the virtual layer using the UNION operation to get the final output.
For example, assume there is a Web Service that provides the Product Details. According to the limitation of this data source, it is mandatory to pass the Product ID on every request. The implementation of this requirement in Denodo and the working behavior are as follows:
Schema of the base view bv_getallproductdetails:
Set the productid field as obligatory with the “=” parameter and multiplicity of one by executing this VQL:
ALTER TABLE bv_getallproductdetails ALTER SEARCHMETHOD bv_getallproductdetails ( CONSTRAINTS ( ADD productid (=) OBL 1 ) WRAPPER (ws bv_getallproductdetails) ); |
Note: In Denodo 7.0 and previous versions, you can also do this graphically (via GUI) in the Virtual DataPort Administration Tool on the Search methods tab.
You should see now the mandatory fields in the Search methods tab:
Executing the base view will ask for the input:
Using the ‘=’ operator in the query and provide value 1
Only one query is delegated to the data source with the Search Condition as productid = 1
Using IN operator in the query
The query is transformed using the ‘=’ operator with the search condition productid = 1 on one node and productid = 2 on another node.
The output is combined by using a UNION operation
References
The information provided in the Denodo Knowledge Base is intended to assist our users in advanced uses of Denodo. Please note that the results from the application of processes and configurations detailed in these documents may vary depending on your specific environment. Use them at your own discretion.
For an official guide of supported features, please refer to the User Manuals. For questions on critical systems or complex environments we recommend you to contact your Denodo Customer Success Manager.
Recommended resources Recommendations generated by AI
Questions
