You can translate the question and the replies:

Passing operator as interpolation variable

Hello, I would like to pass "Operators" (=,<,>etc.) as interpolation variable in my derived view. Is it possible to achieve or do we have any other alternatives Thanks in advance
user
16-05-2023 11:48:42 -0400
code

9 Answers

Hi, Yes, that is possible in Denodo. Let me illustrate it to you with an example when creating a base view using VQL over a JDBC data source. For more information on how to create a base view using VQL for a JDBC data source refer to the section [JDBC Sources](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources#creating-base-views-from-sql-queries) of the Virtual DataPort Administration Guide. Create Base View from Query: SELECT "field1",...,"fieldn" FROM "table" WHERE "fieldx" @operator "value" Example: SELECT * FROM STORAGES WHERE LONGITUDE @operator 10 You can now query that view. It is important to pass in the operator in brackets '...': SELECT ... FROM "view" WHERE operator = '<' When creating derived views on top of these base views, the derived views will inherit the interpolation variable(s) und you must also pass in the necessary value(s) when querying them. This way you can use the interpolation variables in your derived ways. Hope this helps!
Denodo Team
17-05-2023 06:08:59 -0400
code
Thanks. I tried when my value is numeric it is working perfectly ex: SELECT * FROM STORAGES WHERE LONGITUDE @operator @value SELECT * FROM STORAGES WHERE LONGITUDE @operator 10 (I pass @operator is '=' & @value is 10) But if my value is string Ex: SELECT * FROM STORAGES WHERE City @operator @value and I pass @operator is '=' & @value is 'Georgia' , it fails to execute the query. When I check the execution trace I could see in the SQL Sentence SELECT * FROM STORAGES WHERE City = Georgia Note: It is not considering single quotes value Even I try passing @operator is '=' & @value is ''Georgia'' , still it is failing. can you let me know, am I missing anything or any other work around
user
17-05-2023 07:00:44 -0400
Hi, I could see from your example that @value is not surrounded by sing. When dealing with Text/String Data Types you have to put the interpolation variable into single quotes when defining them in the base view. So instead of writing: SELECT FROM STORAGES WHERE City @operator @value You have to put @value into single quotes, as you'd do with other Text data elements: SELECT FROM STORAGES WHERE City @operator ***'@value'*** Let me know if that works for you!
Denodo Team
17-05-2023 09:37:17 -0400
code
Perfect. It is working when I surrounded by single quotes while defining the interpolation variable on my baseview. I checked them on couple of sources like DB2,SQL, etc...it is working, but the same is not working if my data source is bigquery. I am getting the below error Exception [Simba][BigQueryJDBCDriver](100032) Error executing query job. Message: Unparseable query parameter `` in type `TYPE_INT64`, Bad int64 value: = value: '=' can you let me know, am I missing anything or any other work around for this issue. Thanks
user
22-05-2023 10:24:23 -0400
Hi, I tested it with BigQuery too and also got an error message, although not exactly the same one. I believe this might be a limitation of the BigQuery JDBC Driver when handling operators in the form of literals as parameters. To fix this go to the the Configuration of your BigQuery Data Source and under *Source configuration* set *Allow literal as parameter* to *no*. Hope that will solve the issue!
Denodo Team
23-05-2023 10:30:47 -0400
code
Thanks, it works. I have one more scenario, which is I dont want to add interpolation variable in baseview as many of the department using the same base view. So I have to pass "Operators" (=,<,>etc.) as interpolation variable in my derived view directly. In derived view, we have option to add "View Parameter", but using that I have to attach a where clause which is not valid for my scenario (as I am passing "operator" as interpolation) I tried with different option but it is not working. is it possible?
user
01-06-2023 07:26:17 -0400
Hi, What you can do is to use [View Parameters](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/advanced_characteristics/execution_context_of_a_query_and_interpolation_strings/execution_context_of_a_query_and_interpolation_strings#execution-context-of-a-query-and-interpolation-strings) to try and achieve the same logic without changing parameters via interpolation variables. I would recommend to keep creating the base views per introspection via the Design Studio or the Administration Tool instead of from SQL Query with interpolation variables, unless you have a good reason why you cannot use the introspection. This makes the whole setup easier. The end users can still choose different operators to filter for specific columns and can achieve the same logic as is possible when passing operators as parameters (instead of specifying them in a where condition) If you want to define one or multiple fields as mandatory, so that the view can only be executed when a where condition to that field is provided, you could also go with the feature ["Query capabilities"](https://community.denodo.com/docs/html/browse/latest/en/vdp/vql/creating_a_base_view/query_capabilities_search_methods_and_wrappers/query_capabilities_search_methods_and_wrappers) instead of defining interpolation variables. Hope this information helps you!
Denodo Team
05-06-2023 08:08:55 -0400
code
Thank you for the reply. I am not clear with the latest explanation. can you explain or point me "**What is introspection**" . How can I use it or how this would be useful.
user
06-06-2023 05:58:14 -0400
Hi, I introspection I meant that you can create your views graphically using the tools mentioned. In my opinion it makes it alot easier to model your data. Hope this answer clarifies what I meant!
Denodo Team
06-06-2023 06:50:21 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here