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.

Derived OSQL functions are functions defined by queries.

Example:

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)

This query returns the divisors of 143:

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 rules

Case rules compute values that have 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 rules is the built in function atan2(y,x):

sourcecode("atan2")

atan2(1,2)

The case rules 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 rules 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 rules can be used for specifying decision rules based on values of variables.

Tests in case statements succeed unless they return null.

Example:

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

AND, OR, NOT and EXISTS

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.

To negate disjunctive expressions we can use the expression not exists b that returns true is the bag b is empty:

Example:

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

The expression exists b test whether there is some value in a bag b.

Example:

exists range(1,10000000000)

Queries

Case rules​

AND, OR, NOT and EXISTS​

Case rules

AND, OR, NOT and EXISTS