You can translate the question and the replies:

Pass list of value to an interpolation variable

Please help. Query 1: Select * from classicmodels.orders where status in (@input_status) During execution I am passing input_status in '(select "Disputed" from classicmodels.orders)' and getting correct result. But .. Query 2: Select * from classicmodels.orders where status in ('@input_status') During execution I am passing input_status in ('Disputed','Shipped') Not getting any result although the same query is fetching result in VQL SHELL. How to pass list of hard coded values ?
user
04-09-2015 10:55:57 -0400
code

1 Answer

Hi! If you define a base view using an interpolation variable (@input_status in your case), when executing a query on the view the interpolation variable gets replaced in the SQL query that you used to define the view with the value that you used in the where condition of the view. In your second example you defined the view with the following query: Select * from classicmodels.orders where status in ('@input_status') and you are passing the input_status as: input_status in ('Disputed','Shipped') so the query pushed down to the data source will be: Select * from classicmodels.orders where status in ('('Disputed','Shipped')') Note the single quote surrounding the value. This makes the whole condition a single text and the query is looking for orders with a status value of "('Disputed','Shipped')" which does not exist. To achieve what you want you have to define the view in the same way that you list for "Query 1". This option will work both for the subquery and for the list of constant values but note that the list of constant values also needs to be specified as a text: ' "Disputed","Shipped" ' Hope this helps!
Denodo Team
08-09-2015 04:55:47 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here