You can translate the question and the replies:

Confused behavior of Multiplicity in search methods

All, I have been evaluating verson 6.0 express edition. I have a base view generated from Oracle , Name : emp , There Date type column EmpJdate , is nothing but employee joining date. In serach methods I have following setup for EmpJdate Type - Date Operators - = Mandatory - OBL Multiplicity - 1 Values - NULL/Empty So I am assuming that in query one occurrance of EmpJDate is must , It should throw error if I enter more than one value of EMPJDATE, but strange thing, it is not throwing any error and generates result. I was expecting following SQLs to fail 1. select empname,sum(salary) from emp where empjdate in ('21-MAY-2018','15-MAY-2018','01-MAY-2018') group by empname; 2. select empno,empname,salary from emp where empjdate = '21-MAY-2018' OR empjdate = '15-MAY-2018'; Following SQL Failed select empno,empname,salary from emp where empjdate >= '21-MAY-2018' ; Error - Finished with error: View without search methods After above error I again ran below statement suessesfully select empno,empname,salary from emp where empjdate in ('21-MAY-2018' ,'15-MAY-2018'); Can someone please help me to understand how multiplicity is expected to work. Thanks, Adinath Kamode
user
21-05-2018 08:03:03 -0400
code

1 Answer

Hi, Search Methods are used to define the possible query capabilities on the base view. These define which queries can be executed over a data source. Some data sources (e.g. Web sources or Web services) may allow only limited query capabilities on its data, and in those cases, Administrators can define them on the Search Methods tab. As you are using the relational database (Oracle) which supports any type of algebraic operations, Denodo allows to use “=”, “IN” and “OR” operators on the WHERE clause even though you set the Operator to “=” and Multiplicity to “1” on the Search Methods tab. During the execution, Denodo tries to convert the given query by following the rules defined in the Search Method, if it is not possible to convert, then it will throw the error. On the first two queries, you used the operators “IN”, “OR” and “=”, all these can be converted as the limited set of SELECT queries with “=” operations and the final output can be arrived using UNION. Hence, they are executed successfully. You can understand this better when you see the [Execution Trace](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/querying_views/execution_trace_of_a_statement) of the query. On the third query, you used “>=” operator, which cannot be converted as the limited set of SELECT queries with “=” operations. Hence, it has failed. Refer to the following documents to get more details on the usage of Search Methods: * [Query Capabilities](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/advanced_configuration_of_views/query_capabilities#search-methods) section of the Virtual DataPort Administration Guide. * [Query Constraints](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/creating_a_base_view/query_capabilities_search_methods_and_wrappers/query_constraints) section of the Virtual DataPort VQL Guide. Hope this helps.
Denodo Team
23-05-2018 08:18:37 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here