You can translate the document:

Introduction

In this document we will describe some use cases that can be implemented using Denodo VQL Stored Procedures.

Denodo has a procedural language to create stored procedures that allows to invoke VQL statements and use elements such as conditions and loops. These are called VQL stored procedures. Check the documentation for Developing VQL Stored Procedures to learn the basics on how to create them.

Note: this article is based on Denodo Platform 8.0 update 20230914.

Dataset Generation

For this use case, we will create a series of VQL Stored Procedures designed to generate various types of values. These procedures are designed to serve a range of data generation purposes, and each one is tailored to specific requirements. These Stored Procedures may be very useful if we need to generate random values for making some tests or for generating calculated values.

Generating random integer values

The following VQL code shows how to create a stored procedure that generates a specified number of random values (num_rand_values) within the interval [start_number, end_number].

  1. CREATE OR REPLACE VQL PROCEDURE generate_random_values
  2. (num_rand_values IN INT, start_number IN INT, end_number IN INT, random_value OUT INT)
  3. AS (
  4.     random_number INTEGER;
  5.     cnt INTEGER;
  6. )
  7. BEGIN
  8.     cnt:=0;
  9.     WHILE cnt < num_rand_values LOOP
  10.         random_number:=(rand()*(end_number-start_number)+start_number);
  11.         RETURN ROW (random_value) VALUES (random_number);
  12.         cnt:=(cnt+1);
  13.     END LOOP;
  14. END;

In the code we create a LOOP to generate as many numbers as the value of num_rand_values (9), calculate each random number, storing it in random_number (10), and finally we return each value in a row (11).

Execution example

So, if we execute this stored procedure like in the following screenshot:

the output will be:

We can also  join these values with an existing view if we need some test values. A way of making this is with a NESTED JOIN without any join condition:

  1. SELECT id, product_id, distribution_code, storage_id, random_value
  2. FROM vql_stored_procedures.bv_wo_stock NESTED JOIN generate_random_values()
  3. WHERE num_rand_values = 1 and start_number = 0 and end_number = 100;

This will return all the selected view fields and an extra column (random_value) with a random value for each row:

Generating list of the last day of a months interval

This stored procedure returns all the months’ last day between 2 dates that we must enter (startdate and enddate).

  1. CREATE OR REPLACE VQL PROCEDURE getmonthenddate
  2. (startdate IN TIMESTAMP, enddate IN TIMESTAMP, monthenddate OUT TIMESTAMP)
  3. AS (
  4.     processdate TIMESTAMP;
  5.     lastdaymonth TIMESTAMP;
  6.           finaldate TIMESTAMP;
  7.           null_value INT;
  8. )
  9. BEGIN
  10.     lastdaymonth:=TRUNC(lastdayofmonth(startdate));
  11.     finaldate:=TRUNC(enddate);
  12.     WHILE lastdaymonth <= finaldate LOOP
  13.         RETURN ROW (monthenddate) VALUES (lastdaymonth);
  14.         lastdaymonth:=TRUNC(lastdayofmonth(addmonth(lastdaymonth, 1)));
  15.     END LOOP;
  16. END;

We calculate the last day of the month of the interval start date (startdate), we truncate the time part of the timestamp (to save us some problems when comparing dates)  and we store it in another variable (lastdaymonth) (10). We also truncate the end date (enddate) for the same reason and we store it in a new variable (finaldate) (11). Then, in a LOOP we return the last day of the month’s iteration (lastdaymonth) and add one month to that same date until it becomes later than the end date (enddate) (12-15).

Execution example

One example of use is when we have a view containing various cases, each associated with an opening date and a closing date, and we want to have a row for each last day of the month (e.g. to know how many months a worker has worked in a case). We can make a JOIN between the view and the stored procedure selecting in the join condition the input variables of the stored procedure:

  1. SELECT case_id, case_status, case_summary, monthenddate
  2. FROM vql_stored_procedures.bv_cc_case_definition cd JOIN vql_stored_procedures.getmonthenddate() g ON (g.startdate = cd.date_raised AND g.enddate = cd.date_closed)
  3. WHERE cd.date_closed IS NOT null;

Procedure as Custom Function

Stored procedures also can be used to apply, manually or automatically, transformations in the data. This transformation can be applied to a dataset through a JOIN. Additionally, by treating the procedure as a custom function, we can enhance flexibility and adapt the transformation according to specific requirements. For example, a useful stored procedure in this case may be one that transforms a column of prices in a currency to another.

Currency converter

The following stored procedure transforms an amount (start_price) in a certain currency (start_currency) to another (transform_currency) either manually, by providing the conversion value (transform_value), or automatically, by extracting the necessary conversion value from an already existing view (conversiontable).

The view with the conversion values must have a specific structure to work with this exact stored procedure, it needs to have a column called base_0 with the three-letter abbreviation of the desired currency for the transformation, and create individual columns for each stored currency conversion value (the name of each column has to be the three-letter abbreviation).

  1. CREATE OR REPLACE VQL PROCEDURE currency_converter
  2. (start_price IN FLOAT, start_currency IN VARCHAR(3), transform_currency IN VARCHAR(3), transform_value IN FLOAT, conversiontable IN VARCHAR, transform_price OUT FLOAT)
  3. AS (
  4.     CURSOR cursorcolumns IS 'SELECT :param1 as new_currency FROM :param2 WHERE base_0 = '':param3'' ';
  5.     cursorcolumncurrency cursorcolumns%ROWTYPE;
  6.     lower_trans_currency VARCHAR(3);
  7.     final_value FLOAT;
  8.     exception1 EXCEPTION;
  9. )
  10. BEGIN
  11.     lower_trans_currency:=lower(transform_currency);
  12.     IF conversiontable is not null  THEN
  13.         OPEN cursorcolumns PARAMETERS (param1, param2, param3) VALUES (lower_trans_currency, conversiontable, start_currency);
  14.         FETCH cursorcolumns INTO cursorcolumncurrency;
  15.         final_value:=round((start_price*cursorcolumncurrency.new_currency), 2);
  16.         RETURN ROW ( transform_price) VALUES (final_value);
  17.     ELSE
  18.         IF transform_value is not null  THEN
  19.             final_value:=(start_price*transform_value);
  20.             RETURN ROW ( transform_price) VALUES (final_value);
  21.         ELSE
  22.             RAISE exception1;
  23.         END IF;
  24.     END IF;
  25. EXCEPTION
  26.     WHEN exception1 THEN
  27.         RETURN ROW ( transform_price) VALUES ('error');
  28. END;

We control with an IF/THEN/ELSE clause the way we are going to obtain the transformed value. If we enter the name of a conversion table as a parameter, it will open a cursor with a SELECT statement (13) and fetch an iteration to recover the desired currency transformed value(14). Then, the final value(15) will be calculated and returned into a row(16). If we enter the transformed value instead of the conversion table name, the final value will be directly calculated(19) and returned into a row(20). In the case that neither of the two parameters have been entered, an error defined in the exceptions section(25) will be thrown(22).

Execution Example

An example of use of this stored procedure is for example if we have a view that contains the price in USD of some shop articles and we need to have the price in EUR, ARS or any other currency, we can do it easily. We can make a JOIN between the view and the stored procedure selecting in the join condition the inputs variables of the stored procedure:

  1. SELECT p.productid, cc.start_price, cc.start_currency, cc.transform_price, cc.transform_currency
  2. FROM vql_stored_procedures.bv_product p JOIN vql_stored_procedures.currency_converter() cc ON cc.start_price = p.productprice
  3. WHERE cc.conversiontable = 'vql_stored_procedures.iv_currencies_converter_selection' and cc.start_currency = 'EUR' and cc.transform_currency = 'ARS' and cc.transform_value = null;

Denodo Metadata Generation & Maintenance

In the next examples, we are going to see some VQL stored procedures designed to generate, modify or assign metadata for the columns of a view or itself for a view. For example, a useful stored procedure is one that creates or assigns tags to some table or column depending on some criteria.

Add prefix to all columns of a view

The following stored procedure adds an entered prefix (prefix) to all the columns of a selected view (input_view_name) of a database (input_database_name). It will return the default name of the column (output_column_name) and the new name with the prefix added (output_column_name2).

  1. CREATE OR REPLACE VQL PROCEDURE prefix_base_view_columns_controlled
  2. (input_database_name IN VARCHAR, input_view_name IN VARCHAR, prefix IN VARCHAR, output_column_name OUT VARCHAR, output_column_name2 OUT VARCHAR)
  3. AS (
  4.     oldcolname VARCHAR;
  5.     newcolname VARCHAR;
  6.     CURSOR cursorcolumns IS 'SELECT column_name FROM GET_VIEW_COLUMNS() WHERE input_database_name='':param1'' and input_view_name = '':param2''';
  7.     cursorcolumnname cursorcolumns%ROWTYPE;
  8.     exception1 EXCEPTION;
  9. )
  10. BEGIN    
  11.     OPEN cursorcolumns PARAMETERS ( param1, param2 ) VALUES (input_database_name, input_view_name);
  12.     BEGIN_TRANSACTION;
  13.     LOOP
  14.         FETCH cursorcolumns INTO cursorcolumnname;
  15.         oldcolname:=cursorcolumnname.column_name;
  16.         newcolname:=prefix||cursorcolumnname.column_name;
  17.         EXECUTE 'ALTER TABLE :param3 (ALTER COLUMN :param4 RENAME :param5)' PARAMETERS( param3, param4, param5 ) VALUES ( input_view_name, oldcolname, newcolname);
  18.         RETURN ROW ( output_column_name,output_column_name2) VALUES (oldcolname,newcolname);
  19.         EXIT WHEN cursorcolumns%NOTFOUND;      
  20.     END LOOP;
  21.     COMMIT;
  22.         EXCEPTION
  23.              WHEN OTHERS THEN
  24.              RETURN ROW ( output_column_name,output_column_name2) VALUES ('error','not expected');
  25. END;

First of all, we open a cursor with a select statement(6) to extract all the column names of the view. Then, in a LOOP, we save each column name with the prefix added into the variable newcolname(16), execute the corresponding statement to change the default column name to the one with the prefix added(17).

The way to use this stored procedure is executing the following VQL code from a VQL Shell:

Execution example

  1. SELECT *
  2. FROM vql_stored_procedures.prefix_base_view_columns_controlled()
  3. WHERE input_database_name='vql_stored_procedures' AND input_view_name='bv_conn_events' AND prefix='event_';

Or

  1. CALL prefix_base_view_columns_controlled('vql_stored_procedures', 'bv_conn_events', 'events_');

Assign Executes privileges in a folder

The next stored procedure will assign ‘Execute’ privileges to a specific existing role (role_name) for all the contents within a folder (input_folder_name) of a database (input_database_name). It will return a message saying that the role’s privileges have been updated successfully (result).

  1. CREATE or REPLACE VQL PROCEDURE assign_execute_privileges_folder
  2. (input_database_name IN VARCHAR, input_folder_name IN VARCHAR, role_name IN VARCHAR, result OUT VARCHAR)
  3. AS (
  4.     privileges_ddl_statement VARCHAR;
  5.     CURSOR cursorprivileges IS 'SELECT ''ALTER ROLE :param1 GRANT CONNECT ON :param2 ''|| group_concat('' '', ''GRANT READ ON ''|| database_name ||''.''|| name) as ddl_statement FROM GET_VIEWS() WHERE input_database_name = '':param3'' AND folder = '':param4'';';
  6.     cursorprivilege cursorprivileges%ROWTYPE;
  7.     exception1 EXCEPTION;
  8. )
  9. BEGIN
  10.     OPEN cursorprivileges PARAMETERS(param1, param2, param3, param4) VALUES(role_name, input_database_name, input_database_name, input_folder_name);
  11.     LOOP
  12.         FETCH cursorprivileges INTO cursorprivilege;
  13.         privileges_ddl_statement:=cursorprivilege.ddl_statement;
  14.         EXECUTE ':param1' PARAMETERS(param1) VALUES (privileges_ddl_statement);
  15.         RETURN ROW (result) VALUES (privileges_ddl_statement);
  16.         EXIT WHEN cursorprivileges%NOTFOUND;      
  17.     END LOOP;
  18. END;

We open a cursor to obtain, in a table of one row and one column (one iteration inside a LOOP), the DDL statement we want to execute to give the desired privileges to a specific role (10). Then, we enter into a LOOP where we will extract the statement from the table (12) and execute it (14). Finally, if the execute command (14) doesn’t cause any error, we will return a row with a confirmation message (15).

Execution example

  1. SELECT *
  2. FROM assign_execute_privileges_folder()
  3. WHERE input_database_name = 'vql_stored_procedures' AND input_folder_name = '/1 - connectivity/2 - base views' AND role_name = 'test_role_fp';

Or

  1. call assign_execute_privileges_folder('vql_stored_procedures', '/1 - connectivity/2 - base views','test_role_fp');

Create/Assign a tag to columns or views

This stored procedure creates and assigns a tag (tag) to a specific column (input_column) or view (input_view_name), or only assigns the tag if it already exists. It can also be applied to all columns in a database (input_database_name) in general or selectively to those in a view (input_view_name) that match a regular expression (input_column_regexp).

  1. CREATE OR REPLACE VQL PROCEDURE create_tag
  2. FOLDER = '/2 - stored procedures'
  3. (input_database_name IN VARCHAR, input_view_name IN VARCHAR, input_column IN VARCHAR, input_column_regexp IN VARCHAR, tag IN VARCHAR, tagged_table OUT VARCHAR, tagged_column OUT VARCHAR, succesfuly_created OUT BOOL)
  4. AS
  5. (
  6.     CURSOR cursorcolumns IS 'SELECT view_name, column_name FROM GET_VIEW_COLUMNS() WHERE input_database_name='':param1'' AND column_name regexp_like '':param2''';
  7.     cursorcolumnname cursorcolumns%ROWTYPE;
  8.  
  9.     column_name VARCHAR;
  10.     view_name VARCHAR;
  11.  
  12.     null_value VARCHAR;
  13. )
  14. BEGIN
  15.     null_value := null;
  16.     CASE
  17.         WHEN input_column_regexp is not null and input_column is null THEN
  18.             OPEN cursorcolumns PARAMETERS (param1, param2) VALUES (input_database_name, input_column_regexp);
  19.             BEGIN_TRANSACTION;
  20.             LOOP
  21.                 FETCH cursorcolumns INTO cursorcolumnname;
  22.          
  23.                 view_name:=cursorcolumnname.view_name;
  24.                 column_name:=cursorcolumnname.column_name;
  25.            
  26.                 EXECUTE 'CREATE OR REPLACE TAG :param1
  27.                         ADD_TO (VIEWS () COLUMNS (:param2.:param3.:param4))
  28.                         REMOVE_FROM (VIEWS () COLUMNS ())'
  29.                    PARAMETERS (param1, param2, param3, param4) VALUES (tag, input_database_name, view_name, column_name);
  30.                 RETURN ROW (tagged_table, tagged_column, succesfuly_created) VALUES (view_name, column_name, TRUE);
  31.                 EXIT WHEN cursorcolumns%NOTFOUND;
  32.             END LOOP;
  33.                 COMMIT;
  34.         WHEN input_column is not null and input_view_name is not null and input_column_regexp is null THEN
  35.                 BEGIN_TRANSACTION;
  36.             EXECUTE 'CREATE OR REPLACE TAG :param1
  37.                     ADD_TO (VIEWS () COLUMNS (:param2.:param3.:param4))
  38.                     REMOVE_FROM (VIEWS () COLUMNS ())'
  39.                 PARAMETERS (param1, param2, param3, param4) VALUES (tag, input_database_name, input_view_name, input_column);
  40.             RETURN ROW (tagged_table, tagged_column, succesfuly_created) VALUES (null_value,input_column, TRUE);
  41.             COMMIT;
  42.    
  43.         WHEN input_view_name is not null and input_column is null and input_column_regexp is null THEN
  44.                 BEGIN_TRANSACTION;
  45.             EXECUTE 'CREATE OR REPLACE TAG :param1
  46.                     ADD_TO (VIEWS (:param2.:param3) COLUMNS ())
  47.                     REMOVE_FROM (VIEWS () COLUMNS ())'
  48.                 PARAMETERS (param1, param2, param3) VALUES (tag, input_database_name, input_view_name);
  49.             RETURN ROW (tagged_table, tagged_column, succesfuly_created) VALUES (input_view_name, null_value, TRUE);
  50.             COMMIT;
  51.        
  52.     END CASE;
  53. END;

First of all, we define the 3 possible inputs for this stored procedure, when a regular expression is provided(17), when a specific input column is provided(34) and when only a view name is entered(43). For the first case, we take all the column names and the view names, to which they belong, that match with the regular expression(18), then we make a LOOP to take each pair (view_name, column_name)(23-24) and assign the tag(26-29). For the second case, as we know the exact column of the view that we want to assign the tag, we just execute the create tag statement for a column(36-39). Finally, the last case is when no condition about columns is entered and we want to assign a tag to a select view(43), we just execute the corresponding statement(45-48).

Execution examples

Regular expression case

  1. SELECT *
  2. FROM create_tag()
  3. WHERE input_database_name='vql_stored_procedures' AND input_view_name=null AND input_column=null AND input_column_regexp='value.*' AND tag='value_tag';

Or

  1. SELECT *
  2. FROM create_tag() ct JOIN bv_tags t ON ct.input_column_regexp=t.tag_regexp AND ct.tag=t.tag
  3. WHERE input_database_name='vql_stored_procedures' AND input_view_name=null AND input_column=null;

Being bv_tags a view with two columns: tag is the column with the tag we want to assign and tag_regexp is the column with the regular expression of each tag. This is a way to automate the process of creating and assigning tags to different columns with regular expressions.

Selected column case

  1. SELECT *
  2. FROM create_tag()
  3. WHERE input_database_name='vql_stored_procedures' AND input_view_name='bv_cc_agent' AND input_column='agent_id' AND input_column_regexp=null AND tag='id_tag';

Selected view case

  1. SELECT *
  2. FROM create_tag()
  3. WHERE input_database_name='vql_stored_procedures' AND input_view_name='bv_cars' AND input_column=null AND input_column_regexp=null AND tag='cars_tag';

Generate descriptions for columns based on an API call

The last stored procedure generates and assigns a description to all the columns of the input view (database_name.view_name), that did not have one already, by making a call to a previously created view that generates the descriptions (descr_database_name.descr_view_name).  In our case, we are using a view that makes a call to the REST API of ChatGPT, sends it a prompt with the instructions, receives the results in JSON format and returns the descriptions obtained.

  1. CREATE OR REPLACE VQL PROCEDURE assign_descriptions_to_columns
  2. (database_name IN VARCHAR, view_name IN VARCHAR, descr_database_name IN VARCHAR, descr_view_name IN VARCHAR, modified_column OUT VARCHAR, column_description OUT VARCHAR)
  3. AS
  4. (
  5.     CURSOR generatedescriptions IS 'SELECT table_description, coalesce(column_remarks, description) as description_text, field_name FROM GET_VIEW_COLUMNS('':param1'', '':param2'') JOIN (SELECT * FROM :param3.:param4 WHERE database_param = '':param5'' AND view_param = '':param6'') ON field_name = column_name';
  6.     generatedescription generatedescriptions%ROWTYPE;
  7.  
  8.     column_name VARCHAR;
  9.     description_text VARCHAR;
  10.     view_description VARCHAR;
  11. )
  12. BEGIN
  13.     OPEN generatedescriptions PARAMETERS (param1, param2, param3, param4, param5, param6) VALUES (database_name, view_name, descr_database_name, descr_view_name, database_name, view_name);
  14.     LOOP
  15.         FETCH generatedescriptions INTO generatedescription;
  16.        
  17.         column_name := generatedescription.field_name;
  18.         description_text := generatedescription.description_text;
  19.         EXECUTE 'ALTER TABLE :param1 (ALTER COLUMN :param2 ADD (DESCRIPTION = '':param3''))' PARAMETERS( param1, param2, param3 ) VALUES (view_name, column_name, description_text);
  20.         RETURN ROW (modified_column, column_description) VALUES (column_name, description_text);
  21.        
  22.         view_description := generatedescription.table_description;
  23.         EXECUTE 'ALTER TABLE :param1 DESCRIPTION = '':param2''' PARAMETERS(param1, param2) VALUES (view_name, view_description);
  24.        
  25.         EXIT WHEN generatedescriptions%NOTFOUND;
  26.     END LOOP;
  27. END;

We open a cursor that will be responsible for making the call to the view that generates descriptions (13). Inside a LOOP, we FETCH each one of the rows of the cursor (15) that will have a description for the view, the name of the column and a description of the column (if the column already has a description, we will ignore the generated description). At the end, we execute two ALTER TABLE statements, one for assigning the columns descriptions (20)  and one for the view description (23), and we return a row to prove that the description of each column has been correctly assigned (21).

Execution example

  1. SELECT *
  2. FROM vql_stored_procedures.assign_descriptions_to_columns()
  3. WHERE database_name='vql_stored_procedures' AND view_name='bv_cars' AND descr_database_name='vql_stored_procedures' AND descr_view_name='dv_04_chatgpt_question'

Or

  1. CALL assign_descriptions_to_columns('vql_stored_procedures', 'bv_cars', 'vql_stored_procedures', 'dv_04_chatgpt_question');

Encapsulate Operations

The stored procedures are a good option to simplify some everyday operations that may be a little complex but are always carried out in the same way. For example, the action of inserting or updating a row can be a good operation to encapsulate since it may be carried out several times and it is simpler and easier to see how to change a parameter than to modify a statement.

Insert data into a table

The following stored procedure inserts a row with specified values (inputlist) inside a view (database_name.view_name). It’s important to know that we have to enter certain values in a specific format (e.g. the value of inputlist needs to have the structure “column1value, column2value, column3value, …” and if one of the values is a string it must be enclosed in double quotes ‘‘). If everything is correct we will receive a row with an output column with a true value.

  1. CREATE OR REPLACE VQL PROCEDURE insert_row
  2. (inputlist IN VARCHAR, database_name IN VARCHAR, view_name IN VARCHAR, correct_insert OUT BOOL)
  3. AS
  4. (
  5.     CURSOR cursorcolumns IS 'SELECT column_name FROM GET_VIEW_COLUMNS() WHERE database_name = '':param1'' and view_name = '':param2''';
  6.     cursorcolumn cursorcolumns%ROWTYPE;
  7.     columns_string VARCHAR;
  8.     str_cursorcolumn VARCHAR;
  9.     string_size INT;
  10. )
  11. BEGIN
  12.     columns_string := '';
  13.     OPEN cursorcolumns PARAMETERS (param1, param2) VALUES (database_name, view_name);
  14.     LOOP
  15.         FETCH cursorcolumns into cursorcolumn;
  16.         str_cursorcolumn := cursorcolumn.column_name;
  17.         columns_string := CONCAT(columns_string, str_cursorcolumn);
  18.         columns_string := CONCAT(columns_string, ', ');
  19.         EXIT WHEN cursorcolumns%NOTFOUND;
  20.     END LOOP;
  21.     string_size := LEN(columns_string);
  22.     columns_string := SUBSTRING(columns_string, 0, string_size-2);
  23.     EXECUTE 'INSERT INTO :param1.:param2 (:param3) VALUES (:param4)' PARAMETERS (param1, param2, param3, param4) VALUES (database_name, view_name, columns_string, inputlist);
  24.     RETURN ROW(correct_insert) VALUES (true);
  25. END;

We take the name of the columns of the view to which we want to insert a row (13), thanks to the cursor in which we use the predefined VQL stored procedure GET_VIEW_COLUMNS(). In the LOOP we build a string(16 - 18), in the variable columns_string, with the insert statement structure. Once the LOOP is finished, we finish shaping the string(21-22) and execute the insert statement(23).

Execution example

  1. SELECT *
  2. FROM insert_row()
  3. WHERE inputlist = '1234, ''department'', 13, 34' AND database_name =
  4. 'vql_stored_procedures' AND view_name = 'bv_hr_departments';

Or

  1. CALL insert_row('1234, ''department'', 13, 34', 'vql_stored_procedures', 'bv_hr_departments');

Automate insert rows from a view

  1. CREATE OR REPLACE VQL PROCEDURE insert_new_rows_department
  2. (input_database IN VARCHAR, input_view IN VARCHAR, insert_database IN VARCHAR, insert_view IN VARCHAR, insert_row OUT VARCHAR)
  3. AS
  4. (

CURSOR cursorinputs IS 'SELECT department_id as column1, department_name as column2, manager_id as column3, location_id as column4 FROM :param1.:param2';

  1.           cursorinput        cursorinputs%ROWTYPE;
  2.           inputlist VARCHAR;
  3.           
  4. )
  5. BEGIN

          OPEN cursorinputs PARAMETERS (param1, param2) VALUES (input_database, input_view);

  1.     LOOP
  2.             FETCH cursorinputs INTO cursorinput;
  3.         inputlist := cursorinput.column1||', '''''||cursorinput.column2||''''', '||cursorinput.column3||', '||cursorinput.column4;
  4.         EXECUTE 'SELECT * FROM vql_stored_procedures.insert_row() WHERE inputlist = '':param1'' AND database_name = '':param2'' AND view_name = '':param3''' PARAMETERS (param1, param2, param3) VALUES (inputlist, insert_database, insert_view);
  5.         RETURN ROW (insert_row) VALUES (inputlist);
  6.         EXIT WHEN cursorinputs%NOTFOUND;
  7.     END LOOP;
  8. END;

This stored procedure automatizes the action of inserting rows by providing a view with new data and can be executed as the basic insert_row procedure:

  1. SELECT *
  2. FROM insert_new_rows_department()
  3. WHERE input_database = 'vql_stored_procedures' AND input_view = 'bv_new_department_data' AND insert_database = 'vql_stored_procedures' AND insert_view = 'bv_hr_departments';

Or

  1. CALL insert_new_rows_department('vql_stored_procedures', 'bv_new_department_data', 'vql_stored_procedures', 'bv_hr_departments')

Alternative Sources

VQL stored procedures offer a versatile approach to selecting data from alternative sources based on different factors like availability, data quality, or user preferences. We can adapt our stored procedures to make them decide the data source to access based on some condition that might depend on some logic defined in any table. An example of use can be to select the most updated data, or completely new, between two views.

Reading data from different views depending on the update date

This stored procedure is going to compare 2 views (input_baseview1 and input_baseview2) with a identifier column (id), a value column (value) and a update moment (update_time) and it will select the most recent data information (with the ‘higher’ timestamp at update_time).

  1. CREATE OR REPLACE VQL PROCEDURE select_newest_data
  2. (input_database1 IN VARCHAR, input_baseview1 IN VARCHAR, input_database2 IN VARCHAR, input_baseview2 IN VARCHAR, id_column OUT VARCHAR, value_column OUT FLOAT, time_column OUT TIMESTAMP)
  3. AS
  4. (
  5.     CURSOR cursortable1 is 'SELECT id, value, update_time FROM :param1.:param2';
  6.     cursortable1value cursortable1%ROWTYPE;
  7.     CURSOR cursortable2 is 'SELECT id, value, update_time FROM :param1.:param2';
  8.     cursortable2value cursortable2%ROWTYPE;
  9.  
  10.     existing_car BOOL;
  11.     id_table1 VARCHAR;
  12.     id_table2 VARCHAR;
  13.  
  14.     time_table1 TIMESTAMP;
  15.     time_table2 TIMESTAMP;
  16.  
  17.     value_table1 FLOAT;
  18.     value_table2 FLOAT;
  19. )
  20. BEGIN
  21.     OPEN cursortable1 PARAMETERS(param1, param2) VALUES (input_database1, input_baseview1);
  22.     LOOP
  23.         FETCH cursortable1 INTO cursortable1value;
  24.         OPEN cursortable2 PARAMETERS(param1, param2) VALUES (input_database2, input_baseview2);
  25.         existing_car := False;
  26.         LOOP
  27.             FETCH cursortable2 INTO cursortable2value;
  28.             id_table1 := cursortable1value.id;
  29.             id_table2 := cursortable2value.id;
  30.             IF id_table1 = id_table2 THEN
  31.                 existing_car := True;
  32.                 time_table1 := cursortable1value.update_time;
  33.                 time_table2 := cursortable2value.update_time;
  34.                 CASE
  35.                     WHEN time_table1 >= time_table2 THEN
  36.                         value_table1 := cursortable1value.value;
  37.                         RETURN ROW(id_column, value_column, time_column) VALUES(id_table1, value_table1, time_table1);
  38.                     WHEN time_table1 < time_table2 THEN
  39.                         value_table2 := cursortable2value.value;
  40.                         RETURN ROW(id_column, value_column, time_column) VALUES(id_table2, value_table2, time_table2);
  41.                 END CASE;    
  42.             END IF;
  43.             EXIT WHEN cursortable2%NOTFOUND;
  44.         END LOOP;
  45.         IF existing_car = False THEN
  46.             value_table2 := cursortable2value.value;
  47.             RETURN ROW(id_column, value_column, time_column) VALUES(id_table2, value_table2, time_table2);
  48.         END IF;
  49.         EXIT WHEN cursortable1%NOTFOUND;
  50.     END LOOP;
  51. END;

First, we open a cursor to iterate through each row (21), one by one, of one of the views (input_database1.input_baseview1) from which we are going to extract the newest data. We make a LOOP to take all the values of a row of the first view and open an equal cursor for the other view (input_database2.input_baseview2) (22-24). Then, we enter another LOOP (26) where we extract all the values from a row of the second view (27), compare the identifier of each row to determine if they correspond to the same data (28-30) and, if so, compare the value of the update_time column to know which is more recent and return the newest values into a row (34-40). If a row of the second view doesn’t match with any of the first view, we take this row as a new data and we return its values into a row too (45-47).

Execution example

An example of use of this stored procedure can be if we have 2 views with different updated data and we want to obtain the newest data between both tables into a new view. We can create a view with all the updated data as follows:

  1. CREATE VIEW iv_cars_updated
  2. FOLDER = '/3 - integration'
  3. AS SELECT id_column as id, value_column as value, time_column as time
  4. FROM vql_stored_procedures.select_newest_data()
  5. WHERE input_database1='vql_stored_procedures' AND input_baseview1='bv_cars_new' AND input_database2='vql_stored_procedures' AND input_baseview2='bv_cars_old';

References

Developing VQL Stored Procedures

Predefined Stored Procedures

Demo: Empowering Data Processes with Stored Procedures

Questions

Ask a question

You must sign in to ask a question. If you do not have an account, you can register here