# Queries

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

This is an example of a query selecting the odd elements among the integers between 10 and 15:

`select i`

from Integer i

where i in range(10,15)

and odd(i)

The following query returns the name and values of the constants in
the stored function `const(c)`

whose closest integer is 3:

This query requires that you have defined the `const()`

function. This was done in the previous part Basic functions. So if the query fail you might need to go back and define the `const()`

function again.

`select c,const(c)`

from Charstring c

where round(const(c))=3

Queries may both filter, transform, and generate collections. For example, the following query computes the set of divisors for the integer 144:

`select d`

from Integer d, Integer n

where d in range(2,ceiling(n/2))

and 0 = mod(n,d)

and n = 144

Queries my be used for defining derived functions in models, for example:

`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)

The following query returns the divisors of 143:

`divisors(143)`

Queries can be made over different kinds of collections. For example,
the following set query returns the prime numbers in the vector
`[197,143,2]`

:

`select n`

from Integer n

where n in [197,143,2]

and notany(divisors(n))

If you are familiar with
SQL you will notice that `select`

expressions of SQL and OSQL are similar. SQL's `select statement`

is
proved to be very effective for querying and transforming tables in
databases. However, variables in SQL's `select`

statement must be
bound only to rows in uniform tables, while variables in OSQL select
expressions 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 easy to express with OSQL.

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** define functions and formulas that have different
definitions depending on their arguments. For example, a `signum(x)`

function 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

The built-in function `sign(x)`

does the same. Another example of a
functions defined in terms of case rules is the built in
function `atan2(y,x)`

:

`sourcecode("atan2")`

In general, case rules compute values conditioned on values of
variables. 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.

## AND and OR and then SOME

Select statements use AND clauses to join conditions that put contstraints on the output. Sometimes you want to have output that form disjoint components in the solution space. This is acheived by joining conditions with OR. However, in OSQL OR has to be used inside an AND clause with the keyword `some`

.

For example, consider the following query which outputs the values of the vector `[1,2,3,4,5]`

if the values are less than 3 OR larger than 4:

`select i`

from Integer i

where i in [1,2,3,4,5]

and some(i<3 or i>4);

To get the negation of some we can use the keyword `notany`

:

`select i`

from Integer i

where i in [1,2,3,4,5]

and notany(i<3 or i>4);

SOME uses a return early pattern, so the entire expression within the SOME clause does not have to finish before SOME returns true.

For example, comparing the run times between

`[sa.engine] 1> count(iota(1,10000000));`

10000000

0.328 s

and

`[sa.engine] 1> some(iota(1,10000000));`

TRUE

0.016 s

clearly shows that `some`

exits on the first element while `count`

has to step through all elements to determine the number of elements.

## Binding variables with "argmax"

OSQL supports the argmax operation that finds the argument that gives the maximum value of a vector.

For example, the following query finds the index that gives the maximum of the vector `[4,3,9,1,8,5]`

:

`select i`

from Integer i, Vector v

where v = [4,3,9,1,8,5]

and v[i] = max(v);

Similarly you can use argmin to find the argument that gives the minimum value of a vector:

`select i`

from Integer i, Vector v

where v = [4,3,9,1,8,5]

and v[i] = min(v);

Should there be more than one index that satifies the relation, then all of them are emitted:

`select i`

from Integer i, Vector v

where v = [4,3,9,1,9,5]

and v[i] = max(v);

For details on how to make general queries go to Queries in the Topics section.

The next tutorial shows how to develop models, undo changes, and save the database of models on disk.