CREATE VIEW

Description

The CREATE VIEW statement defines a view on one or more tables, views or nicknames.

Click here to navigate to the IBM DB2 docs page for this syntax.

Grammar Syntax

Navigate to the following pages to get more details about the translation spec for the subsections of the CREATE VIEW grammar.

1. CREATE VIEW OF type-name

2. WITH CHECK OPTION

3. WITH ROW MOVEMENT

Examples of Supported Create Views

In order to test a Create View we are going to need a Table with some values, let's look at the following code about a Table with some Inserts.

DB2

CREATE TABLE TestTable
(
	ID INT,
	NAME VARCHAR(10)
);

Insert into TestTable Values(1,'MARCO');
Insert into TestTable Values(2,'ESTEBAN');
Insert into TestTable Values(3,'JEFF');
Insert into TestTable Values(4,'OLIVER');

Snowflake

CREATE TABLE PUBLIC.TestTable
(
	ID INT,
	NAME VARCHAR(10)
);

Insert into TestTable Values(1,'MARCO');
Insert into TestTable Values(2,'ESTEBAN');
Insert into TestTable Values(3,'JEFF');
Insert into TestTable Values(4,'OLIVER');

Now that we have a Table with some data, we can do a couple of examples about a Create View.

DB2

CREATE VIEW ViewTest AS SELECT *  FROM TestTable WHERE ID > 2;
Select * from ViewTest;

Snowflake

CREATE VIEW PUBLIC.ViewTest
AS SELECT *  FROM TestTable WHERE ID > 2;
Select * from ViewTest;

If we compare the results of DB2 and Snowflake we can notice that these are equivalent

DB2

Snowflake

Another example, this one is with a Common Table Expression

DB2

CREATE VIEW ViewTest2 AS WITH TableAlias As (Select * from TestTable where ID < 3) Select * from TableAlias;
Select * from ViewTest2;

Snowflake

CREATE VIEW PUBLIC.ViewTest2
AS WITH TableAlias As (Select * from TestTable where ID < 3) Select * from TableAlias;
Select * from ViewTest2;

If you check the next results, they are both equivalent.

DB2

Snowflake