You can translate the question and the replies:

Create a view using 'List Roles;' Denodo Virtual DataPort datasource

Created new JDBC type data source and select Denodo Virtual DataPort as the Database adaptor. I was trying to create a view using 'List Roles;' using ‘Create from query’ but got the following error when executing the view. Finished with error: Error executing query. Total time 0.985 seconds. BV_LIST_ROLES [BASE] [ERROR] BV_LIST_ROLES [JDBC WRAPPER] [ERROR] BV_LIST_ROLES#0 [JDBC ROUTE] [ERROR] Received exception with message 'Syntax error: Exception parsing query near 't0'' The VDP datasource works well and I can create other views from it. The o/p of 'List Roles;' as I can see in the VQL shell is not a table. Maybe this is causing the issue? Can you help on this. Also please provide if there is any exclusive documentation on this.
user
05-06-2020 08:51:42 -0400

3 Answers

Hi, Generally this issue occurs when the view is created using non-select statement (For example: List Roles). However, I was able to resolve the similar issue by following below steps: * Open the created base view and navigate to "**Options > Search Methods > wrapper source configuration**" section. * Set the property "**Delegate SQL Sentence as Sub Query**" as "**No**". * Once this is done, you could save and execute the view successfully. For more details, you can refer to [View Configuration Properties](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_derived_views/advanced_configuration_of_views/view_configuration_properties#view-configuration-properties) section of the Virtual DataPort Administration Guide. Hope this helps!
Denodo Team
08-06-2020 07:25:54 -0400
Thanks! this helped. I created one more view using ''Desc role "@varible_name";'' and gave the same settings and it got successfully executed. While we execute desc role in VQL we get 4 columns- role name, description, roles and privileges but creating a view gives role name, description, roles columns. How to get the privileges column in the created view.
user
11-06-2020 06:30:27 -0400
Hi, I was facing the similar issue when I try to create a view using DESC role command. However, I was able to get the information about the privileges granted to a user and/or a role by using CATALOG_PERMISSIONS Predefined Stored Procedure. To create a view using this Predefined Stored Procedure, you can follow the below steps: * Open VQL Shell and execute the similar VQL like below to create the view: `create or replace view <view_name> as select * from catalog_permissions()` * Once the view is created, open the view and select "Edit>Model>View parameters" to create a parameters of your choice(for example: 'param_UserName' and 'param_RoleName') and leave the Default value column blank. * Next, navigate to the 'Where conditions' tab and provide the conditions in the following example format: ``` catalog_permissions.username = param_UserName catalog_permissions.rolename = param_RoleName ``` Now, when you execute this view, you will be prompted to enter values for the view parameters and the results will be based on these values. For more details, you can refer to [CATALOG_PERMISSIONS](https://community.denodo.com/docs/html/browse/latest/vdp/vql/stored_procedures/predefined_stored_procedures/catalog_permissions#catalog-permissions) section of the Virtual DataPort VQL Guide. 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
19-06-2020 07:16:36 -0400
You must sign in to add an answer. If you do not have an account, you can register here