Parameters and bindings

A query that always reads the same four weeks is no use on a dashboard. Values that arrive from outside — a period, a line, a defect type — therefore do not sit in the query text. They are parameters.

$1 to $n, and their names

In the query text a parameter stands at its position: $1, $2, $3. It gets a name beside it, in the tag’s parameter list. Both, because the position says where the value is put in and the name says who fills it.

The list follows the text. Type a $4 into a working query and a fourth parameter appears with the provisional name param4. Names already chosen are left exactly as they are — renaming one would break the binding that feeds it.

Values are never written into the query text. They go to the database as real bind parameters. That is the boundary at which an entry from a widget stops being text.

A parameter is bound to another tag

On the Bindings tab of a query tag you connect each parameter to another tag. At read time the value of that tag is put in. The binding is stored as the source tag’s stable id: rename the source and the binding holds.

An unbound parameter stays empty. What it then matches depends on how the query uses it.

from and to

When the builder declares a period, the two parameters are called from and to — exactly that. A board’s period filter fills parameters by name; a query whose time parameters are called anything else is one the date picker cannot reach.

The builder writes the period like this:

($1::timestamptz IS NULL OR "ProductionReport.Timestamp" >= $1::timestamptz)
AND ($2::timestamptz IS NULL OR "ProductionReport.Timestamp" < $2::timestamptz)

Two properties of that are needed in daily use:

• Empty means open. An unfilled to means “no upper end”, not “nothing matches”. Hence the IS NULL half.
• Half-open interval. Lower bound included, upper bound excluded. Two adjacent periods therefore neither overlap nor drop a row between them.

Who fills the parameters is stated at the period as a sentence: if from and to are bound to tags, the hint names those tags. If they are not, it says nothing feeds them yet. Usually they are memory tags such as Plant.Filter.From and Plant.Filter.To, which the board’s filter widgets write into.

How far a value reaches

A query tag’s reach follows from its bindings. If a parameter hangs on a tag that has its own value per user or per view, the result reaches exactly that far — and not across the whole tenant. Without such a binding the figure is the same one for everybody.

In the dry run

The dry run has an input box for every declared parameter. An empty box goes in as NULL, not as an empty text. That is the same distinction as above: an open end as against a value nothing matches.