You can translate the question and the replies:

Parameterised Query

Hello, I came across one issue. Actually, i am creating view through SQL query. In my SQL query there is IN condition in where clause and the array of string values are used in IN condition. The values i am passing through GET request when i am calling REST API. Consider that the below given is my SQL query. Select name,mobile from TestTable where uname IN ('test1', 'test2', 'test3'). Now here i am given the values ('test1', 'test2' ,'test3') at the time of view creation. it works fine. But I want that to be parameterised so i will pass value through querystring in GET request. So i have made my SQL query as: Select name,mobile from TestTable where uname IN ('@username'). When i am executing above query to create view it gives me error. So i want to know that how can declare array of string as variable in SQL query for creating Denodo view so that it work perfect. Eventhhough I remove quotes from the @username, it gives me error as it consider values as a attribute. So please help me with this issue.
user
18-02-2020 09:17:25 -0500

3 Answers

Hi, Denodo Restful Web service has a endpoint called ‘$filter’ that can be used to pass values in the GET request of the REST API. For more information, you can refer to [Input Parameters of the RESTful Web Service](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/restful_architecture/restful_web_service/input_parameters_of_the_restful_web_service) under ‘Restful Architecture’ of the Virtual DataPort Administration Guide. I tried doing the following steps: Create a base view using ‘Create from query’ option including the filter column (**uname**) as well part of the base view. Query: ``` Select name,mobile,uname from TestTable; ``` Publish the base view as REST web service and in the next window, Right-click on the **uname** field and click **Remove from output** and deploy the same. Open the link and add the $filter endpoint to the URL. Sample URL: > http://<host of the denodo server>:9090/server/<name of the database>/<name of the REST API>/views/<name of the base view>?$filter=uname%20IN%20(<input parameter1,input parameter2,....input parameterN) You can also refer to [RESTful Web Service](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/restful_architecture/restful_web_service/restful_web_service) under ‘Restful Architecture’ of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
19-02-2020 08:11:41 -0500
Thank you for your help. But what if i want to apply filter on two column like same as above In condition then how can do?
user
19-02-2020 09:54:51 -0500
Hi, If you want to apply a filter on two columns, you can do it by adding ***ampersand symbol(&)*** next to the $filter condition, Sample URL: > http://<host of the denodo server>:9090/server/<name of the database>/<name of the REST API>/views/<name of the base view>?$filter=field_name%20IN%20(<input parameter1,input parameter2,....input parameterN)&$filter=field_name<condition> Hope this helps!
Denodo Team
26-02-2020 03:40:42 -0500
You must sign in to add an answer. If you do not have an account, you can register here