Basic (scalar) SELECT

Syntax

SELECT [SKIP number_of_rows_to_skip] [TOP num_rows] {*|scalar_column_expr
 [AS alias_name],...}
 FROM [database_name.]object_name
 [WHERE condition]
 [ORDER BY column_name_or_alias [ASC|DESC],...];

Use the optional SKIP clause to specify that the first number_of_rows_to_skip number of rows is excluded from the result set. You can use an ORDER BY clause to exclude the highest or lowest result values. If you do not specify an ORDER BY clause, rows are excluded in a non-deterministic manner. You can use the SKIP clause and the TOP clause together to page results. If you use a SKIP clause with a value that is larger than the number of rows in the result set, then no rows are returned. The SKIP clause is not allowed in aggregate select statements or view select statements, or in select statements that contain subselect clauses or an evaluate clause.

Use the optional TOP clause to display only the first num_rows number of rows of the query results that match the selection criteria. If you include a TOP clause, you must also include an ORDER BY clause to order (sort) the selected rows.

Following a column or virtual column, you can include the AS keyword followed by an alias. This alias is a replacement heading for the column or virtual column name, and is displayed in the query results. If you specify a column alias, use that alias in any references in the ORDER BY clause. The maximum length of a column name or alias is 40 characters.

If you include a WHERE clause, only rows satisfying the criteria specified in the condition are returned.

Use the optional ORDER BY clause to display the results in sequential order depending on the values of one or more column names, in either descending (DESC) or ascending (ASC), order. If the ORDER BY clause is not specified, no ordering is used. If you have specified a column alias by using the AS keyword, use that alias in any references in the ORDER BY column list rather than the corresponding column name.

Examples

select * from alerts.status where Severity = 4;

select * from alerts.status where Node like 'terminal.*';

select Severity, Severity + Tally from alerts.status;

select Severity, Severity + Tally as Real_Severity from alerts.status;

In the following example, the Node column is sorted in ascending order and the first two rows are excluded from the result set:

select skip 2 Node from alerts.status order by Node asc;

You can use the SKIP and TOP clauses together to page results. In the following example, the Node column is sorted in ascending order, the first two rows are excluded from the result set, and the top five results are selected:

select skip 2 top 5 Node from alerts.status order by Node asc;

Using row variables in subselect clauses

A reference to a row variable in a subselect clause (that is, a nested SELECT statement) is permitted only if the row variable is on a different nesting level.

create or replace procedure test1()
begin
    for each row status_row in alerts.status where status_row.Identifier not in
            (select status_row.Identifier from alerts.test_tab )
    begin
        ....
    end;
end;

create or replace procedure test1()
begin
    for each row t_group in catalog.trigger_groups
    begin
        for each row trig in catalog.trigger_stats where
            trig.TriggerName in ( select TriggerName from catalog.triggers
						 where GroupName = t_group.GroupName )
        begin
            ...
        end;
    end;
end;