Typed views

You can create a typed view when you want to distinguish between two views that display data of the same data type. For example, suppose you want to create two views on the following table:

CREATE TABLE emp
(  name    VARCHAR(30),
   age     INTEGER,
   salary  INTEGER);

The following statements create two typed views, name_age and name_salary, on the emp table:

CREATE ROW TYPE name_age_t
(  name   VARCHAR(20),
   age    INTEGER);

CREATE VIEW name_age OF TYPE name_age_t AS
   SELECT name, age FROM emp;

CREATE ROW TYPE name_salary_t
(  name    VARCHAR(20),
   salary  INTEGER);

CREATE VIEW name_salary OF TYPE name_salary_t AS
   SELECT name, salary FROM emp

When you create a typed view, the data that the view displays is of a named row type. For example, the name_age and name_salary views contain VARCHAR and INTEGER data. Because the views are typed, a query against the name_age view returns a column view of type name_age whereas a query against the name_salary view returns a column view of type name_salary. Consequently, the database server is able to distinguish between rows that the name_age and name_salary views return.

In some cases, a typed view has an advantage over an untyped view. For example, suppose you overload the function myfunc() as follows:

CREATE FUNCTION myfunc(aa name_age_t) ......;
CREATE FUNCTION myfunc(aa name_salary_t) .....;

Because the name_age and name_salary views are typed views, the following statements resolve to the appropriate myfunc() function:

SELECT myfunc(name_age) FROM name_age; 
SELECT myfunc(name_salary) FROM name_salary;

You can also write the preceding SELECT statements using an alias for the table name:

SELECT myfunc(p) FROM name_age p; 
SELECT myfunc(p) FROM name_salary p;

If two views that contain the same data types are not created as typed views, the database server cannot distinguish between the rows that the two views display. For more information about function overloading, see HCL OneDB™ User-Defined Routines and Data Types Developer's Guide.