execute from an output of VQL

Hi , what is the best way to execute output from a VQL statement ? I have something like this select 'alter table ' || view_name || ' (alter column ' || column_name || ' add (description = ''' || column_name || ' ''));' as vql_script from (select * from CATALOG_VDP_METADATA_VIEWS () where input_database_name = 'sandbox' and input_view_name = 'dv_test') where column_description = '' the output give me a few rows of alter table dv_test (alter column name add (description = 'name ')); alter table dv_test (alter column active add (description = 'active '));
user
23-07-2018 18:03:20 -0400

3 Answers

Hi, The most efficient way to execute would be to copy the output, open a VQL Shell and execute the statements. However, based on the naming convention of the example you have given, the statements you have are to run an [ALTER TABLE](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/creating_a_base_view/modifying_a_base_view/modifying_a_base_view) command on “dv_test”, which would be a derived view. This is not possible as only fields of base views can be modified. You would have to change input_view_name = ‘dv_test’ to the base view of the derived view that you wish to make changes to. Hope this helps!
Denodo Team
24-07-2018 05:18:52 -0400
Hi, it is also possible to use the Denodo Scheduler for this job. You can: - Create a VDP Job - In the extraction section - Use @vql_script as Parameterized query: - In the bottom part select "New Source" - VDP and use your query: select ‘alter table ‘ || view_name || ‘ (alter column ‘ || column_name || ‘ add (description = ‘’’ || column_name || ‘ ‘’));’ as vql_script from (select * from CATALOG_VDP_METADATA_VIEWS () where input_database_name = ‘sandbox’ and input_view_name = ‘dv_test’) where column_description = ‘’ That will execute all the Alter queries returned by the CATALOG query Good luck!
user
24-07-2018 10:14:47 -0400
Thank you so much for the quick responses. You both are awesome.
user
24-07-2018 10:18:27 -0400
You must sign in to add an answer. If you do not have an account, you can register here