You can translate the question and the replies:

VQL stored procedure-error cursor

Hi, I am trying to create this stored procedure: ( nomeview IN VARCHAR) AS ( // Procedure variables v_name VARCHAR; database_name VARCHAR; Interface_name VARCHAR; CURSOR interfaces IS 'SELECT view_name FROM VIEW_DEPENDENCIES() WHERE view_database_name = ''vdb_sample'' and dependency_name = :v_name'; ) BEGIN // Procedure body v_name := nomeview; //database_name := nome_db; Interface_name := null; OPEN interfaces PARAMETERS (v_name) VALUES(dependency_name); FETCH interfaces INTO Interface_name; CLOSE interfaces; EXECUTE 'CREATE OR REPLACE view v3 FOLDER = ''/03 - business entities'' AS SELECT * from :Interface_name' PARAMETERS ( Interface_name ) VALUES ( Interface_name); END However, I am encountering this error, and I don't know how to solve it: QUERY [SELECTION] [ERROR] GET_INTERFACES [STORED_PROCEDURE] [ERROR] GET_INTERFACES [STORED PROCEDURE] [ERROR] Received exception with message 'Syntax error: Exception parsing query near ':'' Can you please help me?
user
12-12-2023 12:12:06 -0500
code

2 Answers

Hi, This error messages generally indicates that there has been a misconfiguration when using a parameter within the query used to assign a value to your cursor. Try surrounding your ***:vname*** with 2 single quotes: ``` CURSOR interfaces IS 'SELECT view_name FROM VIEW_DEPENDENCIES() WHERE view_database_name = ''vdb_sample'' and dependency_name = '':v_name'''; ``` Hope this helps.
Denodo Team
13-12-2023 08:31:16 -0500
code
Hi everyone. I need to create an interface with dynamic values on the interface definition. I will give an example to show what I mean: Normally an interface is created this way: CREATE OR REPLACE INTERFACE VIEW i_inventory_50 ( productpartnumber_0:text, locationlocationidentifier_0:text, inventorytype:text, quantity:text, quantityunits:text, value:text, valuecurrency:text, reservationorders:text, daysofsupply:text, shelflife:text, reorderlevel:text, expectedleadtime:text, quantityupperthreshold:text, quantitylowerthreshold:text, daysofsupplyupperthreshold:text, daysofsupplylowerthreshold:text, expiringthreshold:text, plannercode:text, velocitycode:text, inventoryparenttype:text, class:text, segment:text ) SET IMPLEMENTATION dv_inventory_50 ** What I´m trying to do looks something like this: CREATE OR REPLACE INTERFACE VIEW i_inventory_50 ( Cast(<SELECT STATEMENT> as text) )SET IMPLEMENTATION dv_inventory_50 Is it possible? Thanks in advance for anyone kind enough to respond.
user
14-12-2023 10:47:05 -0500
You must sign in to add an answer. If you do not have an account, you can register here