Thursday, 23 April 2015

Getting view names and definition of view in Netezza

To get list of all the views in Netezza use the below statement:


SELECT viewname FROM _v_view WHERE viewname NOT LIKE '\_%' AND UPPER(objtype) = 'VIEW' ORDER BY viewname;


To get the definition of a view in Netezza  use the below statement. 


SELECT definition FROM _v_view WHERE viewname = <VIEWNAME> AND UPPER(objtype) = 'VIEW';

To get the unique object id associate with the view in Netezza use the below statement:


SELECT objid FROM _v_view WHERE viewname = <VIEWNAME> AND UPPER(objtype) = 'VIEW';


General syntax to create views in Netezza is :

create view vendor_view as select vendor_id, vendor_name from vendor order by vendor_id;

To read about mat views check:


http://dwbitechguru.blogspot.ca/2014/12/what-is-materialized-mat-view-in.html