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.
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 (
ADD productid (=) OBL 1
WRAPPER (ws bv_getallproductdetails)
Note: In Denodo 7.0 or previous, 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