Denodo – VQL Script To Add A Primary Key to derived Views?

Advertisements

Here is a Denodo Virtual Query Language (VQL) code snippet to add a primary key to multiple derived views in Virtual DataPort (VDP).  This code snippet assumes the primary key is the first field in the table.  Useful when needing to do bulk work on data warehouse-style views where the surrogate key of the view is the first field. I have found the script occasionally helpful when working with a large number of views, which need to be updated.

VQL To Add A Primary Key On A Semantic Views

select ‘ALTER VIEW ‘|| ‘ “‘||View_name||'” ‘|| ‘ADD CONSTRAINT ‘ ||”’PK_’||Column_Name||”’ ‘||’ PRIMARY KEY (”’|| Column_Name || ”’ ) ;’

From(Select AA.view_name,AA.Column_name

from

(

Select View_name, Column_Name

FROM GET_VIEW_COLUMNS()

    WHERE input_database_name=’<<Database_Name>>

    and Ordinal_Position = 1

) AA,

(SELECT Name View_name , folder

    FROM GET_VIEWS()

    WHERE input_database_name = ‘<<Database_Name>>

    AND folder = ‘<<Folder_Path>>‘) AB

where AA.View_name = AB. View_name

)BA

Vendor References

Denodo > User Manuals > Virtual DataPort VQL Guide > Stored Procedures > Predefined Stored Procedures > GET_VIEW_COLUMNS

Denodo > User Manuals > Virtual DataPort VQL Guide > Stored Procedures > Predefined Stored Procedures > GET_VIEWS

Leave a Reply Cancel reply