How to get list of base views which are created from query?

I need to fetch list of all baseviews which are created from query. Kindly suggest how can we get the list in Denodo 6.0
user
11-02-2019 03:40:32 -0500

3 Answers

Hi, I was able to get the list of views that are created from a query on top of JDBC data sources by executing the predefined stored procedure GET_SOURCE_TABLE. For example > call GET_SOURCE_TABLE(‘input_database_name’,’input_viewname’) For more information, you can refer to [GET_SOURCE_TABLE](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/stored_procedures/predefined_stored_procedures/get_source_table#get-source-table) section of Virtual DataPort VQL Guide. Hope this helps!
Denodo Team
11-02-2019 23:08:23 -0500
Hi We already tried using that but it's not working for us: select source_table_name from GET_SOURCE_TABLE('db_corp_dds', 'tnl_accrual_security') Source table name is coming out as empty source_table_name NULL PS: We have created base view with query and a parameter defined. Could you try to create the viewagain from the query but this time use a parameter as well. Data source SQL : SELECT travel.person_id AS person_id ,travel.ssn AS ssn ,travel.source_system_name AS source_system_name ,travel.ifg_code AS ifg_code ,travel.access_status AS access_status FROM ( SELECT b.username AS person_id ,'TRAVEL' AS ssn ,b.source_system_name AS source_system_name ,a.ifg_code AS ifg_code ,'Y' AS access_status FROM ( SELECT DISTINCT source_system_name ,ifg_code FROM xx_travel_accrual_expense WHERE source_system_name = 'TRAVEL' ) a INNER JOIN gen_user_security b ON a.source_system_name = b.source_system_name AND b.book_type_code = '####' WHERE b.username = @person_id AND b.application_name IN ( '####' ,'exp' ) UNION SELECT username AS person_id ,'TRAVEL' AS ssn ,source_system_name AS source_system_name ,book_type_code AS ifg_code ,'Y' AS access_status FROM gen_user_security WHERE source_system_name = 'TRAVEL' AND username = @person_id AND book_type_code <> '####' AND application_name IN ( '####' ,'exp' ) ) travel
user
12-02-2019 00:06:41 -0500
Hi, For a base view created from query, I would refer to the column 'sqlsentence' instead of 'source_table_name'. `select a.sqlsentence as created_from_query, a.* from GET_SOURCE_TABLE('input_database_name','input_viewname') a ` Hope this helps!
Denodo Team
04-04-2019 07:21:07 -0400
You must sign in to add an answer. If you do not have an account, you can register here