You can delimit the SELECT clause with parentheses. If you do, you must specify both opening and closing parentheses or the system aborts the request.
You can invoke an SQL UDF at any point of the SELECT clause of a view definition. The rules are the same as those for a SELECT request.
See SELECT Statements for complete documentation of the SELECT clause.
- DISTINCT
- Returns only one row from any set of duplicates that results from an expression list.
- Two rows are considered duplicates only if each value in one is equal to each corresponding value in the other.
- You cannot specify a DISTINCT operator if the view definition also includes a TOP n or TOP m PERCENT specification.
- ALL
- All rows, including duplicates, are to be returned in the results of the expression list.
- This is the default value.
- TOP
- Returns the top-ranked rows to from the SELECT operation.
- You cannot specify a TOP n operator if the view definition also specifies any of the following:
- DISTINCT operator
- SAMPLE clause
- QUALIFY clause
- Subquery, that is, a subquery within a view definition cannot contain a TOP operator.
- Views that specify a TOP operator are not updatable.
- You can also specify an ORDER BY clause with the TOP operator. Otherwise, ORDER BY clauses are not valid within view definitions.
- The sort expression for the ORDER BY clause cannot contain BLOBs, CLOBs, UDTs, columns with Period or Geospatial types, or ordered analytic functions.
- TOP performs as well or better as the ordered analytic functions QUALIFY RANK() or QUALIFY ROW_NUMBER() that can be used in a similar fashion.
- n
- A DECIMAL or an INTEGER value for the number of rows to return from the SELECT operation.
- This can be more limiting than the TOP n clause you can specify with DML requests, depending on whether you specify an ORDER BY clause or not. If you do not specify an ORDER BY clause, then a TOP n clause only specifies that any n base table rows be returned, not the TOP n.
- m PERCENT
- Returns only m percent of rows from the SELECT operation, where m can be either a DECIMAL or an INTEGER value.
- If ( m *COUNT(*)/100.0) does not evaluate to an integer value, Vantage rounds it up to the next highest integer value. For example:
- If ( m *COUNT(*)/100.0) evaluates to 0.1, Vantage rounds it up to 1.
- If ( m *COUNT(*)/100.0) evaluates to 9.2, Vantage rounds it up to 10.
- Vantage applies this rounding up only to the first 15 digits following the decimal point. For example, if ( m *COUNT(*)/100.0) evaluates to 0.0000000000000001, Vantage does not round it up to 1.
- WITH TIES
- All qualified rows having the same value for the ORDER BY fields are to be included in the results set.
- This option only works if you also specify an ORDER BY clause; otherwise, the system ignores it.
- If you do not specify the WITH TIES option, then the system returns only the first n or m PERCENT qualified rows.
- For example, suppose you specify TOP 2 WITH TIES and ORDER BY OrderValue. If the top customer for this field has an OrderValue of 300,000 USD, but there are two additional customers with the same next highest OrderValue of 295,000 USD, then all 3 are returned in the result set.
- If you omit WITH TIES, only the first of the two customers in the result list having OrderValue 295,000 USD are returned.
- *
- All columns from all tables referenced in the FROM clause are to be returned.
- When qualified by table_name, * specifies that all columns of only the user base table or view specified by table_name are to be returned.
- View columns are explicitly enumerated when views are defined; therefore, if a table is changed after a view is defined, those changes do not appear if the SELECT * construct is used.
sub_selection
A valid SQL expression.
You can specify both aggregate and arithmetic operators in a view definition expression.
You cannot specify a SAMPLE clause if the view definition also includes a TOP n or TOP m PERCENT specification.
The presence of aggregates in a view definition renders that view non-updatable.
- expression_alias
- A temporary name for the expression.
- Alias names are used to name expressions and must be be specified for a self-join operation on the table.