You can translate the question and the replies:


Hi everyone, this is the SP that creates the view interface and it's intended to be created in the indicated database specified in the parameter. How can I do that? [quarta-feira 17:22] Diogo Goncalves - Passio Consulting CREATE OR REPLACE PROCEDURE create_interface_view ( vdb_name IN VARCHAR, derived_view_name IN VARCHAR, interface_name IN VARCHAR, prt OUT VARCHAR ) AS ( vdb VARCHAR; d_name VARCHAR; i_name VARCHAR; frst VARCHAR; decimals VARCHAR; radix VARCHAR; size VARCHAR; remarks VARCHAR; code VARCHAR; result VARCHAR; max_rows INTEGER; max_linha INTEGER; rown INTEGER; vql_inter VARCHAR; prt VARCHAR; CURSOR get_max_rows IS 'SELECT COUNT(*) FROM GET_VIEW_COLUMNS() WHERE input_database_name = :vdb AND input_view_name = :d_name'; CURSOR get_data IS 'SELECT CONCAT(column_name, '':'', column_vdp_type), column_decimals, column_radix, column_size, column_remarks, column_sql_type_code FROM GET_VIEW_COLUMNS() WHERE input_database_name = :vdb AND input_view_name = :d_name'; ) BEGIN vdb := vdb_name; d_name := derived_view_name; i_name := interface_name; max_linha := 1; vql_inter := '('; OPEN get_max_rows PARAMETERS (vdb, d_name) VALUES (vdb_name, derived_view_name); FETCH get_max_rows INTO max_rows; CLOSE get_max_rows; RETURN ROW (prt) VALUES (max_rows); OPEN get_data PARAMETERS (vdb, d_name) VALUES (vdb_name, derived_view_name); LOOP FETCH get_data INTO frst, decimals, radix, size, remarks, code; result := CONCAT(frst, ' (sourcetypedecimals = ''', decimals, ''', sourcetyperadix = ''', radix, ''', sourcetypesize = ''', size, ''', description = ''', remarks, ''', sourcetypeid = ''', code, ''')'); IF max_linha < max_rows THEN vql_inter := CONCAT(vql_inter, result, ','); ELSE vql_inter := CONCAT(vql_inter, result, ')'); END IF; max_linha := max_linha + 1; EXIT WHEN max_linha > max_rows; END LOOP; CLOSE get_data; vql_inter := CONCAT(interface_name, ' ', vql_inter, ' SET IMPLEMENTATION ', derived_view_name, ' FOLDER = ''/03 - business entities'''); RETURN ROW (prt) VALUES (vql_inter); EXECUTE 'CREATE OR REPLACE INTERFACE VIEW :param' PARAMETERS (param) VALUES(vql_inter); END

3 Answers

Hi, It’s my understanding that you want to create the interface view in a specific Database. In this case you can select the Database that you want from the Database option at the top of the VQL Shell page. To avoid getting an error, please start your query using this syntax CREATE OR REPLACE VQL PROCEDURE create_interface_view. Ensure that you drop the previous interface view to not get additional errors. Hope this helps!
Denodo Team
22-04-2024 14:48:56 -0400
My idea is to get a view interface from a vdb and put it in another vdb, using the vql stored procedure.
23-04-2024 10:37:29 -0400
Hi, You can use this VQL Stored Procedure and after you get the interface view in the first Virtual DataBase just Export the interface view and import it again by selecting the other Virtual DataBase name that you want. Hope this helps!
Denodo Team
28-04-2024 10:41:22 -0400
You must sign in to add an answer. If you do not have an account, you can register here