Views in Informix
Views in Informix
|
| Release 9.3 |
   |
Note:This topic was updated for 9.3.1.
Generally speaking, a view is a predefined, named query stored in the database. Once created, views can be queried in much the same way that tables can be queried. Views contain rows and columns, like tables do. However, views are not tables but representations of data stored in, or calculated from, one or more other tables (business tables) or views. The columns of the view are the items from the select list of the query that defines the view.
Creating views can improve query response times by restricting the number of tables and columns searched when a data request is made. You can create views that client applications see as feature classes, and you can create views to bring in data from other tables.
Creating a view with the sdetable create_view operation is much like any other Informix view creation. If you want to create a view using a layer and you want the resulting view to appear as a feature class to client applications, include the feature class's spatial column in the view definition.
For information on privileges needed to create views, consult the Informix documentation.
IDS 10 Infocenter
IDS 11 Infocenter
Querying the sysviews system catalog table
When you create views in an Informix database using SQL, the views are defined in the sysviews system catalog table. The same is true when you create views using sdetable –o create_view. That means you can query the sysviews system catalog table to find out how each view is defined.
The sysviews system catalog table has the following columns:
sysviews| Name |
Type |
Description |
|---|
| tabid |
integer |
ID of the view |
| seqno |
smallint |
Line number of the SELECT statement that created the view |
| viewtext |
char(64) |
The statement used to create the view |
You can first query the systables system catalog table to find the tabid of the view you want. The systables system catalog table stores a record for every database object, but you can query it so only views are returned, as shown in the following example:
SELECT *
FROM informix.systables
WHERE tabtype = 'V'
When you find the view for which you want to see the definition, query the sysviews table.
SELECT *
FROM informix.sysviews
WHERE tabid = <view_id>
Creating a view with a join
The following view returns the employee ID, employee name, and employee salary by joining two tables, EMPLOYEES (owned by the connected user) and SALARY (from the schema PAYROLL):
Ready: sdetable –o create_view –T view_dept_100_salaries –t "employees,payroll.salaries" –c 'employees.objectid,employees.name,payroll.salaries.salary' –w "department = 100 and employees.objectid = payroll.salaries.employeeid" –u gdb –p gdb
If the tables are not owned by the connected user, the name must be in the format owner.table. When using multiple tables, you must qualify the names of each column listed after –c with the name of the table using the format table.column or owner.table.column.
