In ComponentOne Query, the main unit of information stored in a database is called a view. This concept is different from the usual database view, although there are certain similarities. To distinguish a C1Query view from a database view, we will sometimes refer to it as a schema view.
In simple cases, a schema view is just a database table. You will see schema views in one-to-one correspondence with database tables in a schema just imported from a database structure. Unless you need the advanced functionality of multi-table views or multiple views representing a single table, you can leave it at that and just consider the word «view» synonymous to the word «table».
To edit a view, double click on a view in the Views palette, or select a view and choose Edit from the context menu. A View Designer window will open as an MDI child. You can have multiple designer windows open simultaneously in the schema designer. To add a new view to the schema, use the Add new view button on the Views palette.
A single table can be represented by multiple views. In this case, when two views refer to a single table, and both views are used in a user query, the generated SQL statement will include this table twice, with different aliases. This is useful in special situations, for example, in representing self-joins, as with the «is managed by» relationship between the records in an Employee table.
Views can contain calculated fields. A calculated field has an SQL expression which is used in the generated SQL statement. In an expression, use SQL syntax for functions and operators, and the following syntax for table fields:
| Example Title |
Copy Code
|
|---|---|
[table_name].[field_name] |
|
For example,
| Example Title |
Copy Code
|
|---|---|
[Order Details].[UnitPrice] * [Order Details].[Quantity] * (1 - [Order Details].[Discount]) |
|
To add a calculated field to a view, use the Add field button on the top of the View Designer, or select New field from the context menu. Specify the field's Name, Type (and optionally, Size, for fixed length string fields), and the Expression.
You can also specify an arbitrary SQL condition for a view in the Advanced tab of the View Designer. This optional SQL condition will be included in the WHERE clause of the generated SQL statement. To reference view fields in the SQL condition, use the same syntax as in calculated field expressions described above.