You can translate the question and the replies:

Subquery giving an error

Hi I have a VQL query as follows: SELECT * FROM bv_getlistdata WHERE sessionhandle = '123456' and token = (SELECT requesttoken FROM bv_request) The view bv_getlistdata has 2 parameters sessionhandle and token and the bv_request view has a single output column 'requesttoken' which represents the input of the token parameter. The value of the sub query returned is: '{12345,23456,34577}' When I run the query i get an error - it looks like it does not recognize the input for the token parameter: *Finished with error: No search methods ready to be run. The following fields are obligatory: bvgetlistdata.token * However if I run the subquery manually and then input the result directly into the query it works fine: SELECT * FROM bv_getlistdata WHERE sessionhandle = '123456' and token = '{12345,23456,34577}' I have also tried with different settings on the token attribute (OPT, MAN) but same result Is there some way of ensuring that the subquery is executed first to ensure that the subquery result is passed correctly to main query? Thanks in advance

subquery

user
03-10-2019 10:20:27 -0400

3 Answers

Hi, I have come across the same error when I try to pass the subquery as value to the obligatory field in the 'where' clause. The error 'No search methods ready to be run’ is because the VQL query expects the **Obligatory** field 'token' to have a value assigned during the query parsing and currently this is considered a post-filtering condition, so it does not remove obligatoriness over a field and that's the reason the error pops up. There are a couple of ways to overcome the above issue either I would * Remove the “OBL” on the field token and execute the query as below >SELECT * FROM bv_getlistdata WHERE sessionhandle = ‘123456’ and token in (SELECT requesttoken FROM bv_request) . OR * Rewrite the query to use a regular join, for instance > SELECT * FROM bv_getlistdata a , bv_request b WHERE a.sessionhandle = ‘123456’ and a.token = b.requesttoken For more information, you can refer to the documentation [Subqueries in the WHERE Clause of the Query](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/queries_select_statement/from_clause/subqueries_in_the_where_clause_of_the_query) section of the Virtual DataPort VQL Guide. Hope this helps!

Denodo Team
04-10-2019 05:08:31 -0400

I have already tried setting the OBL to OPT but it then returns a different error from the web service data source as follows: *SoapException: Server was unable to process request. ---> selectionToken must be provided * This would suggest to me that the subquery is not resolved and a blank value is being passed with the main query The fact that I am able to successfully run the script by replacing the subquery clause with the actual result of the subquery run manually would suggest to me that the main query has not yet resolved the subquery at the point of execution. Is there some way that i can force the execution of subquery first (eg with CTE or some CONTEXT clause) to ensure that the result is correctly passed Note - I have also tried the 2nd method as indicated using join but the result is still the same.

user
04-10-2019 05:25:14 -0400

Hi, To pass the subquery as value to the obligatory field in the ‘where’ clause. I would pass the query similar as below >SELECT * FROM bv_request a INNER JOIN bv_getlistdata b ON b.token = a.requesttoken WHERE b.sessionhandle = ‘123456’ For more information, you can refer to the [Dynamic Choice of Join Strategy](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/advanced_characteristics/dynamic_choice_of_join_strategy/dynamic_choice_of_join_strategy#dynamic-choice-of-join-strategy) section of the Virtual DataPort VQL Guide. Hope this helps!

Denodo Team
14-10-2019 07:13:30 -0400

ADD ANSWER

You must sign in to add an answer. If you do not have an account, you can register here