602SQL Documentation Index  
<­Generalized Table UpQueries in SQL Query Expression­>

Query Specification (SQL)

query_specification ::= SELECT [ ALL | DISTINCT ] expression [ rename ] { , expression [ rename ] } 
FROM generalized table { , generalized table } 
[ WHERE where_condition ] 
[ GROUP BY expression_group { , expression_group } ]
[ HAVING having_condition ]
rename ::= AS name

The query specification selects or creates a set of records out of the records from the generalized tables.

If the DISTINCT clause is set, only different records will be selected (only for columns of fixed-length). If the ALL keyword is set (or omitted), all suitable records will be selected.

If there are more generalized tables specified, the JOIN operation will be executed on them (each record will be joined with other records). Otherwise, all records from the generalized table are used.

If the WHERE clause is specified, only those records that fulfill the where_condition will be selected.

If the GROUP BY clause is specified, the records will be divided into groups. Each group contains records with the same values of the expression_group expression.

If the HAVING clause is specified, only those groups that fulfill the having_condition will be selected.

The query result is composed from the columns with expression values on selected records or groups of records. In order to evaluate all expressions on these groups, it is required that each expression:

If these conditions are not fulfilled, the contents of this expression are not defined in the result.

Aggregate functions may not be used in the where_condition (except for subqueries). These functions may be used in expressions in the SELECT clause and in the having_condition. If the query specification does not contain the GROUP BY clause, these aggregate functions will be evaluated over the entire query result, rather than each group separately.

The column names in the query result depend on the structure of the< i>expressions. If a expression is composed of a column name of some generalized_table, it will preserve this name, otherwise a unique name will be assigned automatically (e.g. Expr1 etc.). If name duplicity may occur because of name preserving, it can be automatically prevented using the compatibility attribute SQLOPT_DUPLIC_COLNAMES (256) in the Set_sql_option function. You can also specify column names in the specification using the AS clause.

The SELECT expression may also use the * character. If the expression is in the form generalized_table.*, all columns of the generalized_table will be in the result. If the SELECT * clause is used, no other expressions may follow. If you wish to add system columns (beginning with _w5_...) to the query selection using SELECT *, you will have to set the compatibility attribute SQLOPT_USE_SYS_COLS (8192) in the Set_sql_option function.

The expression may also be a column name (or *), constant, CASE statement, standard SQL function or a function created using the DECLARE FUNCTION function, aggregate function, server variable, global client variable, subquery that returns one row and one column or a expression composed of these subexpressions.

Details on creating conditions in a query specification can be found here

Example:

SELECT Month(date),SUM(Orders.ordered)
FROM Companies, Orders
WHERE Orders.firma=Companies.company
GROUP BY Month(date)

See

<­Generalized Table UpQueries in SQL Query Expression­>