You can translate the question and the replies:

Denodo interpolation/parameter issue while using IN clause

Hi, I am facing an issue where using an interpolation variable in where condition of a dervied view. The views is something like this: CREATE OR REPLACE VIEW V1 AS SELECT Col1, Param1 FROM T1 WHERE Col2 = Param1 USING PARAMETERS (Param1 : text '123' ); I am using string 123 as default if no values are pass to interpolation variable at run time. The query runs fines where is use = operator i.e select * from V1 where Param1 = 'ABC' but when I use IN operator to pass mutiple value at run time it passing the value in connrectly eg. select * from V1 where Param1 IN ('ABC', 'XYZ') is giving 0 results and when I checked the delegated SQL I see the query generated is pushng in correct perdicate value rather than a UNION or something where '123' IN ('ABC', 'XYZ') Note: When is don't use a default value for the parameter it works just fine but I need a default value for the view.
Interpolation Variables param IN cl default value
user
29-01-2020 10:52:20 -0500
code

1 Answer

Hi, Currently in Denodo Platform, when there is an obligatory field and the query provides a set of possible values for the field using an IN operator in the WHERE clause, then the Virtual DataPort would generate a union query by providing the obligatory field with each of the possible values. And the behavior is the same if the view parameter does not have a default value. But when there is a default value, the view parameter is not OBL any more, so a union is not generated. Hope this helps!
Denodo Team
04-02-2020 07:07:55 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here