Queries

Queries filter out and transform objects fulfilling some specific properties from collections of objects.

Example:

The following query returns the name and values of the constants in the table Constants whose closest integer is 3:

select name, value
  from Constants
 where round(value)=3

OSQL queries can filter, transform, and generate objects of any kind, not only strings and numbers stored in tables. You can make queries over any kind of objects by specifying their types in the from clause of the select statement.

Examples:

The following query computes the set of divisors for the integer 143:

select d
  from Integer d, Integer n
 where d in range(2,ceiling(n/2))
   and 0 = mod(n,d)
   and n = 143

This query selects the odd elements among the integers between 10 and 15:

select i
  from Integer i
 where i in range(10,15)
   and odd(i)

Note

SQL's select statement is a special case of OSQL queries. A restriction with SQL's select statement is that variables in queries must be bound only to rows in tables. In OSQL queries, variables can be bound to any kind of objects, e.g. strings, numbers, vectors, matrices, tensors, bags, streams, records or user defined objects. Combined with functions, this makes analytical models in different domains very easy to express with OSQL.

The SQL subset of OSQL is introduced in the SQL Tutorial. You can skip that tutorial if you are already familiar with SQL.

This derived function computes the divisors of integer n:

create function divisors(Integer n) -> Bag of Integer
  as select d
       from Integer d
      where d in range(2,ceiling(n/2))
        and 0 = mod(n,d)

divisors(143)

Queries can be made over various kinds of collections, not only sets and bags.

Examples:

The following set query returns the set of prime numbers in the vector collection [197,143,2]:

select n
  from Integer n
 where n in [197,143,2]
   and not exists divisors(n)

The following query returns a vector of prime numbers in [197,143,2]:

select Vector of n
  from Integer n
 where n in [197,143,2]
   and not exists divisors(n)

The following query returns a stream of prime numbers in [197,143,2]:

select Stream of n
  from Integer n
 where n in [197,143,2]
   and not exists divisors(n)

In the tutorial Streams it will be shown how to specify OSQL queries over live streams.

General queries over sets and bags are documented in Queries.

Queries to filter and transform elements in vectors and tensors are documented in Vector queries.

Case expressions

Case expressions compute values having different definitions depending on conditions.

Example:

The signum of a number can be defined as:

create function signum(Real x) -> Integer
  as case when x > 0 then 1
          when x = 0 then 0
          else -1 end

signum(-3.1)

The built-in function sign(x) does the same.

Another example of a function defined in terms of case expressions is the built in function atan2(y,x):

sourcecode("atan2")

atan2(1,2)

The case expression compute these values without executing any actions (side effects). This is different from conditional statements in conventional programming languages such as if {condition} do-something; else do-something-else; where different side effects (actions) are executed based on a condition.

Case expressions can be used in queries, for example:

select case when x <= -40 then "too cold"
            when x < 0    then "freezing"
            when x < 18   then "cold"
            when x < 23   then "plesant"
            when x < 40   then "hot"
            else "too hot"
        end
  from Real x
 where x in 10*range(-4,4)

This illustrates how case expressions can be used for specifying decision rules based on values of variables.

Tests in case expressions can be functions returning any kind of value, not only logical values. A test succeeds unless its value is null or false.

Example:

case when sqrt(-1)    then 1
     when 2 < sqrt(2) then 2
     else 0 end

AND, OR and NOT

In the where clause of a select statement, conditions connected with and operators restrict the query result. Analogously, or clauses extends the query result.

Example:

The following query returns the set of values of the vector [1,2,3,4,5] that are less than 3 or larger than 4:

select i
  from Integer i
 where i in [1,2,3,4,5]
   and (i<3 or i>4)

Notice that or has higher priority than and in queries, so parentheses may be needed if both and and or are used in the same query as in the example.

To negate disjunctive logical expressions we can use the expression not l that returns true is the logical value of l is true:

Example:

select i
  from Integer i
 where i in [1,2,3,4,5]
   and not i<=sqrt(10)

Queries

Case expressions​

AND, OR and NOT​

Case expressions

AND, OR and NOT