When writing queries with multiple clauses you will sometimes want to store values temporarily to use in subsequent clauses, but not want to keep them after the query has finished.
In this case you will want to use variables. They have 3 advantages:
- They are not written to the database so can be quicker.
- They do not use any user fields which you may want to use for other purposes.
- They can be used when setting up GROUP BY or SELECT queries which display results in grids or export them to files.
There are other types of variables which will be described later, this section confines itself to 'normal' variables.
You do not have to explicitly 'declare' variables by saying that you want to use a variable with a particular name, you merely have to use the variable in the query. Variables are distinguished from fields and constants by beginning with the dollar sign $. Apart from the dollar sign letters, digits and the underscore character are valid characters to use in variable names. The first character after the $ must not be a digit.
Thus - $height, $pipe_height, $_pipe_height and $pipe_height_123 - are all valid variable names.
Variables names are case insensitive, so you could use $HEIGHT, $height, $Height, $hEiGhT etc. but they would all refer to the same variable.
It is possible to use variables in association with one-to-one links, which have been explained above, and with one-to-many links, which will be explained later in this paper.
Variables are automatically associated with a particular object type or types, and an error message will be displayed if you attempt to use a variable associated with one object type with another e.g. if you set a variable for nodes then attempt to use it for conduits.
If the first clause you use a variable in is for a particular type of node or link e.g. conduits, then it is automatically associated with all nodes or all links rather than the individual node or link type. This means that if you subsequently use it in a context where any node or any link may be used there will not be a problem. If the variable has been initially assigned in a clause for a particular node or link type, then if it is used in another node or link type, or for all node or link types, then the value of the variable will be NULL for any nodes or links of types for which it has not been assigned.
In the case where you are running multiple queries together by dragging an SQL Query Group onto a GeoPlan, the variables will be preserved between queries i.e. a variable set in one query will be available in a subsequent query. This is the only circumstance in which the variable values outlive the execution of a single query.