You can translate the question and the replies:

WHEREEXPRESSION using a Subquery as a value

We are working with an JSON REST API using a WHEREEXPRESSION in the Post Body and have run into an issue when using a Subquery. Here's an example of our issue. We are able to successfully return data from the Denodo view using the WHEREEXPRESSION: select * from "ApiView" where "WHEREEXPRESSION" = ' owner IN ( ''Joseph'', ''Michael'', ''Derrick'') ' However, we would like to feed the WHEREEXPRESSION values from a different Denodo view query, without having to feed the values manually or creating a two step query process to send the values to the expression. We have attempted the following JOIN example, but WHEREEXPRESSION will not handle the Subquery like below: with view1 as ( select concat('owner IN ', '(', '''''', group_concat(''''',''''', ("Owner")), '''''', ')' ) AS "OwnerList" from "MsSqlView" where "Product" in ("Product1", "Product2", "Product3") ) select * from "ApiView" where "WHEREEXPRESSION" = ( select "OwnerList" from view1 ) Results: Finished with error: No search methods ready to be run. The following fields are obligatory: ApiView.WHEREEXPRESSION The value returned from the CTE is valid and validated when running the CTE separately and manually adding the string returned to the second query's WHEREEXPRESSION value. We have tried setting the WHEREEXPRESSION as OPTional, but that did not resolve the issue. Can a WHEREEXPRESSION handle a Subquery like the example above? Or, does the WHEREEXPRESSION value have to be explicitly set?
user
09-04-2020 17:59:04 -0400

1 Answer

Hi, In order to pass the subquery result as a parameter for the interpolation variable (WHEREEXPRESSION), you could join the view from where you would like to retrieve the value. For your scenario, you could follow below steps in Virtual DataPort Administration Tool: * Create a view ApiView * Create another view using the below query: > select concat(‘owner IN ‘, ‘(‘, ‘’’’’’, group_concat(‘’’’’,’’’’’, (“Owner”)), ‘’’’’’, ‘)’ ) AS “OwnerList” > from “MsSqlView” > where “Product” in (“Product1”, “Product2”, “Product3”) * Once the views are created, perform a join operation over both the views. * In the model tab, join the interpolation field with OwnerList. * From the output tab, you can select and remove all the unnecessary columns and save the view. * Now when you execute the view, you would be able to obtain the expected results. If you still need help and if you are a valid Support User, you may open a Support Case at the Denodo Support Site and the Support Team will help you. Hope this helps!
Denodo Team
16-04-2020 09:24:10 -0400
You must sign in to add an answer. If you do not have an account, you can register here