You can translate the question and the replies:

On base views created from query with parameters-how can we pass the parameters from a subquery in where statement

I have created a base view from query which contains parameters that I need to pass in order to get the result set. Base view name is bv_root_table. Parameter for the base view that is required that we pass is AccountID field. I am trying to execute a select statement on the base view, by passing the accountID values from an inner query, instead of entering them. I am constantly getting "View without search methods" error message. How can I pass the acccount id values from an inner query in where statements, similar to the one below. Is there any other methods I can utilize? SELECT pay_id , original_pay_event_id AS root_pay_event_id FROM database2.bv_root_table WHERE accountid in (select account_id from database.iv_view_name where purchaser_id = '12345') If I run below query, I get results instantly without an issue (similar statement as above, but I hard code the accountid instead of running a subquery). SELECT pay_id , original_pay_event_id AS root_pay_event_id FROM database2.bv_root_table WHERE accountid in ('111222333', '111244555') Business knows the purchaser_id, they don't use account_id field, and they need to be able to pass group_purchaser_id field as criteria. How can I pass the result of another query as a parameter to the base view created from query?

3 Answers

Hi, When passing the subquery as a value to the obligatory field in the ‘where’ clause, the error **‘No search methods ready to be run’** is thrown. This 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. In order to resolve this error, I would rewrite the query as below using [JOIN](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/queries_select_statement/from_clause/join_operation#join-operation) > SELECT pay_id, original_pay_event_id AS root_pay_event_id FROM database2.bv_root_table a > INNER JOIN database.iv_view_name b > ON a.accountid =b.account_id > WHERE purchaser_id = ‘12345’ You can have a look at the similar Community [Q&A](https://community.denodo.com/answers/question/details?questionId=9060g000000XfAXAA0&title=Subquery+giving+an+error) for more information. Hope this helps!
Denodo Team
13-06-2022 07:32:11 -0400
I forgot to disclose that my inner query is a select **distinct**. If I try to use join as you suggested, it would end up resulting in dups in result set. How can I get through eliminating dups in iv_view_name with joins? SELECT pay_id , original_pay_event_id AS root_pay_event_id FROM database2.bv_root_table WHERE accountid in (select distinct account_id from database.iv_view_name where purchaser_id = ‘12345’)
user
13-06-2022 17:15:34 -0400
Hi, I would use **DISTINCT** clause as below to get distinct values in the result: > SELECT DISTINCT > pay_id, original_pay_event_id AS root_pay_event_id > FROM database2.bv_root_table a > INNER JOIN database.iv_view_name b > ON a.accountid =b.account_id > WHERE purchaser_id = ‘12345’ Hope this helps!
Denodo Team
14-06-2022 05:57:06 -0400
You must sign in to add an answer. If you do not have an account, you can register here