Index...
Centrallix Documentation
|
6.1 SELECT Statements
SELECT statements
SELECT [ column_alias = ] expression [, ... ]
[ FROM [ IDENTITY ] [ OBJECT ] [ [ INCLUSIVE ] SUBTREE ] [ WILDCARD ]
pathname [ source_alias ] [, ... ] ]
[ WHERE expression ]
[ GROUP BY expression [, expression ... ] ]
[ ORDER BY expression [ ASC | DESC ] [, ... ] ]
[ HAVING expression ]
[ LIMIT [ start, ] count ]
[ FOR UPDATE ]
Here, the syntax is similar to normal SQL, except that the attribute names are preceded by a colon, and FROM sources are pathnames instead of in the usual SQL format of Database.Owner.Table. Here is a simple example of selecting a single constant value:
SELECT "Hello this is a SQL statement"
When a source is used, it is a pathname:
SELECT :name, :size from /samples
Using aliases on data sources is also possible:
SELECT :s:name, :s:size from /samples s
Attribute names may be quoted in order to permit them to contain special characters and spaces:
SELECT :"Account Number" from /datasource/tables/Account/rows
Attributes may also be renamed. This is particularly useful when using a constant or computed field:
SELECT filename=:name, filesize=:size from /samples
SELECT myConstant="this is a constant value"
Selecting all enumerable attributes (SELECT *)
The wildcard * can be used with SELECT to return all enumerable attributes from the data sources. However, be aware that in some circumstances the attributes will vary from one row to another in the result set; Centrallix does not enforce that all objects in a collection have the same set of attributes, since it has to work with such a variety of data sources. SELECT * does not automatically select the system attributes "name", "inner_type", "outer_type", "content_type", and "annotation". To select those, you can combine SELECT * with explicit columns.
SELECT * from /samples
SELECT :name, * from /samples
You can also include computed values with a SELECT * query:
SELECT *, description = 'File name is ' + :name from /samples
Finally, you can override a normally enumerable attribute, so a particular attribute's value is replaced in the selected result set:
SELECT *, size = 1000 from /samples
Limiting the Result Set Size
Centrallix can limit the number of rows returned in a SQL query either by using the "LIMIT" clause or by using the "set rowcount n" SQL clause, where n is the maximum number of rows to return.
Example:
SET ROWCOUNT 10 SELECT :name, * from /samples
The LIMIT clause is similar to the MySQL limit clause, and is placed somewhere in the SQL statement (normally at the very end). It has the form LIMIT [ start, ] count where start is an optional starting row number (first row is row 0), and count is the desired maximum number of rows to return. LIMIT N is equivalent to SET ROWCOUNT N.
Examples (the first one returns the first 10 rows, 1 through 10, and the second one returns rows 6 through 15):
SELECT :name, * from /samples LIMIT 10
SELECT :name, * from /samples LIMIT 5,10
Assigning Values to Declared Object Properties
If previous DECLARE statement(s) have been used, then SELECT can be used to assign values to properties of a declared object. This is done very similarly to using column aliases (see above), except an :objectname:attributename reference is used instead:
SELECT :tmpobj:filecount = count(1) FROM /samples
Comments...
(none yet)
Add a Comment...
|
