Netezza Create View Syntax and Examples

  • Post author:
  • Post last modified:February 27, 2018
  • Post category:Netezza
  • Reading time:3 mins read

You can use Netezza create view to create a virtual table based on the result-set of a complex SQL statement that may have multiple table joins.

netezza create view

Just like views in other database a Netezza view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. Netezza Views are read-only. The system does not allow you an insert, update, or delete on a view.

Read:

Netezza Create View Syntax

Syntax for creating a view:

CREATE VIEW <view_name> AS SELECT <query>;
CREATE OR REPLACE VIEW <view_name> AS SELECT <query>;

Use the CREATE VIEW command to define a view in the given database. The view is not physically materialized. Instead, a query rewrites retrieve rule is automatically generated to support retrieve operations on views.

Use the CREATE OR REPLACE VIEW to redefine a view and retain the permissions from the original view. Other databases like Teradata supports creating RECURSIVE VIEW but Netezza does not supports RECURSIVE view creation,

Netezza Create View Examples

Create a view that consists of all action films, enter:

TRAINING.ADMIN(ADMIN)=> CREATE VIEW action_movies AS
 SELECT *
 FROM films
 WHERE kind = 'action';

Display the result by executing created view

You can query the view like any other table in the Netezza database. For Example:

TRAINING.ADMIN(ADMIN)=> SELECT * FROM action_movies;
SYSTEM.ADMIN(ADMIN)=> select * from action_movies; 
 CODE | TITLE | KIND | LEN 
-------+------------+--------+---------- 
 C_101 | Globe | Action | 01:30:00 
 C_701 | Golden Eye | Action | 01:36:00 
 C_102 | Globe1 | Action | 01:40:00 
 C_103 | Globe2 | Action | 01:30:00 
 C_104 | Globe3 | Action | 01:36:00 
 C_105 | Globe4 | Action | 01:37:00 
(6 rows)

Create a view that consists of all action films, enter:

TRAINING.ADMIN(ADMIN)=> CREATE OR REPLACE VIEW action_movies AS
 SELECT *
 FROM films
 WHERE kind = 'action';

Netezza Create View command outputs

The Netezza Create view command has following outputs:

CREATE VIEW: You will get this output when view is successfully created

ERROR: Relation ‘view’ already exists: Whatever view you are trying to create is already exists in the mentioned Netezza database

Netezza check View if it is not obsolete

If you alter or change the underlying view table then that view will become obsolete and no usable until you re-compile that. IBM has provided the script that will allow you to check if view is obsolete. Generally, script is available at /nz/support/bin location. Ask your Netezza admin team for exact location. Below is the script and its usage:

nz_check_views [ database ] [-replace <flag>]
Tags: , ,

This Post Has 2 Comments

  1. Chris July 19, 2018

    After I create a view in Netezza, my code is “modified” by the system.
    For example I write :
    create or replace view CLIENT_TEST as
    select c.UCI as ID
    ,c.FAMILY_NAME
    ,c.GIVEN_NAME
    from CLIENT_BASIC c
    where extract(years from age(c.BIRTH_DATE)) >= 18

    and when look for the view definition in the database I get:

    CREATE OR REPLACE VIEW ADMIN.CLIENT_TEST AS SELECT C.UCI AS “ID”, C.FAMILY_NAME, C.GIVEN_NAME FROM ADMIN.CLIENT_BASIC C WHERE (DATE_PART(‘YEARS’::”VARCHAR”, AGE(C.BIRTH_DATE)) >= 18);

    Is there any way to retrieve the original entered code? Or save this code by default in the database, not the “corrected” version?

    Thank you

Comments are closed.