Index...
Centrallix Documentation
|
6.2 WHERE Clause
Selecting Specific Objects
Let's examine selecting specific objects that are of interest. Again, the syntax is similar (a WHERE clause) to standard SQL. First, a simple WHERE clause:
SELECT :name, :size from /samples
WHERE :size > 1024
When aliases are used, they are also to be used in the WHERE clause:
SELECT :s:name, :s:size from /samples s
WHERE :s:size > 1024
The WHERE clause can contain compound statements or function calls:
SELECT :s:name, :s:size from /samples s
WHERE (:s:size < 10240 and :s:size > 1024) and substring(:s:name, 1, 3) == 'tmp'
Joining Multiple Data Sources
As with normal SQL, you can use the WHERE clause to join multiple data sources together. These data sources need not be from the same database, and can involve fundamentally different types of objects. For example, you can perform a SQL join between a database table and a directory of files.
For the equality comparison operator, Centrallix SQL accepts either = or ==.
SELECT :a:city, :a:state, :p:full_name
FROM /myDatabase/Address/rows a,
/myDatabase/Person/rows p
WHERE :a:person_id = :p:id
Outer joins are possible, using Sybase / MS SQL Server outer join syntax (using *= and =* where the 'mandatory' table is on the * side of the *= or =*):
SELECT :d:size, fullname = isnull(:u:fullname,:d:owner)
FROM /mydirectory d, /sysadm/users.uxu u
WHERE :d:owner *= :u:name
Self-joins are also possible. Be sure to use aliases so that you can tell the two references to one table apart:
SELECT :d1:name + "is the same size as" + :d2:name
FROM /mydirectory d1,
/mydirectory d2
WHERE :d1:size == :d2:size
Multiple WHERE Clauses
To facilitate simplicity in building dynamic SQL, Centrallix permits SQL queries to contain more than one WHERE clause. In that case, the multiple WHERE clauses are combined using AND semantics.
SELECT :name, :size
FROM /samples
WHERE :size > 1024
WHERE :size < 10240
Comments...
(none yet)
Add a Comment...
|
