Basic Object Selection

Introduction to basic arithmetic and comparison

In its simplest form, SQL may be used to selected objects for which an expression is true. This section focuses on arithmetic and string comparisons.

The available arithmetic operators are:

+	Addition
-	Subtraction
*	Multiplication
/	Division
^	Exponent (e.g. width^2, area^0.5)
%	Modulus

It is also possible to use '-' to negate a number, this is known as 'unary minus'.

These arithmetic operators follow the normal operator precedence, with unary minus having highest precedence, then exponent, then division and multiplication, then finally addition and subtraction.

For example a * b + c means multiply a and b, then add c. You can use brackets to override these precedence rules e.g. a * (b + c) means add b and c then multiply by a. There is nothing to stop you from using brackets where they are not needed in order to clarify your intention. There is no harm in writing (a * b) + c instead of a * b + c.

You can also use the addition operator '+' to join strings. For example, if user text 1 were 'X12' and user text 2 were 'Y34' then user_text_1 + user_text_2 would yield 'X12Y34'.

None of the other operators listed above have any special meaning for strings. The behaviour if you do use one of these operators with two strings, or with a string and a number etc. will be described in detail in a later section.

You can use comparison operators to compare values to see if they are equal, one is greater than the other etc.

The available comparison operators are:

=	Equality
>=	Greater than or equal to
>	Strictly greater than
<=	Less than or equal to
<	Strictly less than
<>	Not equal to

The comparison operators have a higher precedence than the arithmetic operators. This means that if you say something like a + b * c >= d + e * f , the left and right hand sides of the comparison operator are evaluated, then the comparison is done.

There are three things to note about string comparison in InfoAsset Manager:

1. String comparison is always case insensitive.
2. When using less than, greater than, greater than or equal to and less than or equal to, the behaviour will depend on the behaviour of the language in which you are using Windows.
3. Strings are always trimmed so that they have no leading or trailing spaces or 'tab' characters i.e. a string can never begin with a string or a tab.

Having described the arithmetic and comparison operators, the next question that will spring to mind is what can be manipulated with the arithmetic operators and what can be compared.

We will start by considering two things: fields and constants. Other things, including variables, are explained in other SQL topics.

Fields can be found in the pull down lists on the SQL dialog. The fields fall into a number of categories:

1. Fields in the current table e.g. node_id, us_node_id, x, y, width, height
2. Fields in joined tables. These fall into two distinct categories:
   1. One-to-one links – links where there can be only one object linked – e.g. the link from a link to its upstream node is a one-to-one link since any given link can only be linked to one (or zero) upstream node
   2. One-to-many links – links where there can be more than one object linked – e.g. the link from a node to links upstream of it is a one-to-many link since any given node can have zero, one, two or more links upstream of it.
3. Fields in 'structure blobs' e.g. details in CCTV surveys

As well as these four main categories, there are some special cases which will be described later in this paper.

The fields in the current table have the meaning you would expect. The others will be explained in later sections.

You may wonder what happens when you have selected more than one table in the pull-down list of tables e.g. 'All Nodes', 'All Links', and a given object is of a type that does not have a particular field e.g. if the object for which the query is being evaluated is a conduit and the field is specific to a flap valve. This will be explained in the next section once the special value used has been described.

Constants fall into 5 types:

1. Numbers – simply put the number in, with or without decimal points and the minus sign e.g. 123, -123, 123.45, -123.45.
2. Strings – strings should begin and end with the single quote character ' e.g. 'mynodeid' or the double quote character e.g. "mynodeid". If you begin a string with one of these you must end it with the same one. To include the single quote character within a string, use the double quotes at the beginning and the end of the string. To include the double quote character within a string, use the single quotes at the beginning and the end of the string .
3. Boolean – The Boolean data type and the two constant values will be described later in this paper.
4. Dates – Date constants begin and end with the # character. The rules are as follows:
   1. Behaviour depends on the ‘locale’ setting if your PC
   2. If you are in the UK or another country using dates of the form dd/mm/yyyy then dates should be entered #day/month/year# e.g. #31/1/2008#.
   3. If you are in the US or another country using dates of the form mm/dd/yyyy then dates should be entered #month/day/year# e.g. #1/31/2008#.
   4. If you are in Japan, China or another country using dates of the form yyyy/mm/dd then dates should be entered #2008/31/1# .
   5. It is possible, but not recommended to enter dates without using all 4 digits in the year (e.g. #1/31/08#). The behaviour of 2 digit years is determined by the regional and language options control panel – see http://support.microsoft.com/kb/214391 for details.
   6. It is also possible but also not recommended to enter dates of only two parts e.g. #1/12#. In both of these cases the underlying date library will resolve this as described in the above Microsoft knowledge base article.
   7. If you are using dates of the form dd/mm/yyyy or mm/dd/yyyy and you enter a date which is invalid in the form you are using, but valid in the other of these two forms, the date will be interpreted as the other e.g. if you are in the US and enter #31/1/2008#, this is an invalid date since there is no 31st month, however it would be valid as a UK date, so it is treated as being the 31st of January 2008.
5. NULL – this is a special value, described in the next section.

NULL field values and associated operators

In InfoAsset Manager, numerical fields can usually be blank. In SQL this is represented as a special value called 'NULL', and the fields are said to 'be NULL'. You can see if fields are / aren’t NULL by using the special constructs 'IS NULL' and 'IS NOT NULL' e.g. ground_level IS NULL, ground_level IS NOT NULL.

We are now in the position to answer the question posed above, as to what value is returned for objects that do not have a particular field when the query is being evaluated for a number of different types of object e.g. 'All Links'. The answer is simply that the NULL value is returned for any object that does not have the field in question.

Logical operators

You can combine the expressions built up so far with the logical operators AND, OR and NOT e.g.

x>10000 AND y>20000 will select nodes where the x coordinate is greater than 10000 AND the y coordinate is greater than 20000.

x>10000 AND y>20000 will select nodes where the x coordinate is greater than 10000 AND the y coordinate is greater than 20000.

As with all implementations of SQL and in common usage for programming languages 'A or B' means A is true, B is true OR both A and B are true. This is often referred to as 'inclusive or', and is slightly different from the way in which the word 'or' is sometimes used in day to day English. When the keyword NOT is used it will select objects for which the expression to which the NOT applies is not true.

The keyword AND has higher precedence than the keyword OR, so A OR B AND C OR D means the same thing as (A OR B) AND (C OR D). As with the other operators described above it is possible to override this precedence rule by using brackets, so you can say things like A OR (B AND C) OR D.

String operators

There are two operators which can be used to perform more sophisticated tests on strings than the tests described above. These operators are LIKE and MATCHES.

LIKE may be used to perform relatively simple tests on strings by testing for prefixes and a match in the number of characters in a string. This is done by using the special characters ? and *. ? matches any character * matches the rest of the string.

Any other character matches only that character itself – in common with the string comparisons described above this matching is case insensitive.

e.g.

node_id LIKE 'MH12345' will match only the string MH12345, since no special characters are included in the string on the right of the LIKE operator.

node_id LIKE '????????' will match any 8 character node ID

node_id LIKE 'MH??????' will match any 8 character string beginning MH

node_id LIKE '??1????' will match any node ID with 1 as the 3rd character

node_id LIKE 'MH*' will match any node ID beginning with MH

node_id LIKE 'MH??*' will match any node ID at least 4 characters long beginning with MH.

You cannot check for anything after * has been used in the pattern on the right of the LIKE operator, e.g. node_id LIKE '*01' cannot be used to match any node ID ending in 01. Similarly you cannot use * at the beginning and end to match something in the middle, so you cannot say node_id LIKE '*01*' to match any node ID with 01 somewhere in the middle.

MATCHES may be used to perform complex tests on strings by seeing if they match regular expressions.

A full description of regular expressions falls outside the scope of this paper. Note:

1. The regular expression matching, in common with the string comparisons described above and the LIKE keyword, is case insensitive.
2. The regular expression much match the whole of the string i.e. node_id MATCHES '[0-9]*' will only match node_ids containing only digits.

Data types and type conversion rules

Data types

The InfoAsset Manager databases contain data values of a number of types. These types are as follows:

1. A number of numerical types. From the point of view of SQL the differences between these types may largely be ignored as the numerical calculations are all performed using the same numerical data type (double precision floating point numbers).
2. String
3. Boolean. The 'Boolean' data type is named after the British mathematician and philosopher George Boole (which is the reason the word is written with a capital letter at the beginning) and represents something that can either be true or false. In InfoAsset Manager, Boolean values are typically represented by check-boxes which are checked if the variable is true and unchecked if it is false. The two constant values for Boolean variables are 'true' and 'false'.
4. Date. This represents a date and time e.g. 12th January 2003 12:34. The time may or may not be relevant depending on the context in which it is being used.

All numerical calculations within the InfoAsset Manager SQL engine are performed using user units.

The following is a technical description of exactly how calculations are performed, with particular reference to what happens when you combine data types e.g. add a number to a string and then store the result in a Boolean field.

Mixing data types in binary operators

A binary operator is one that takes two values e.g +, -, *, AND, >=. The word binary is used here meaning 'two', in this context it has nothing to do with binary numbers.

When operations are performed with a mixture of data types the following rules are applied in order:

1. If the operator is = and one of the two values being compared is NULL then this will return 'true' only if both of the values being compared is NULL, otherwise it will return 'false'.
2. If the operator is <> and one of the two values being compared is NULL, then this will return 'true' if only one of the values being compared is NULL, if both are NULL then it will return 'false'.
3. If the operator is + and one of the two values is a string, then the other value will be converted to a string (see below) and the two strings concatenated.
4. If the operator is not OR and one of the two values is NULL then the result will be NULL
5. If the operator is OR and one of the two values is NULL, then other value is converted to a Boolean (see below) and that is the value of the expression i.e. if that value is 'false' then the result will be false, otherwise the result will be true.

The above are the only cases where an expression where one of the two values is NULL may not result in a result of NULL. This is in-line with 'normal' SQL behaviour. This is known as '3 valued logic' because in a sense as well as 'true' and 'false' there is a value of NULL. The underlying theory behind this is that NULL represents an absent or unknown value, and therefore if you attempt to perform any operation that combines an absent or unknown value with a known value, the result is still absent or unknown.

The following descriptions assume that neither of rules 4 and 5 have applied and therefore neither of the values is NULL.

1. If the operator is +, -, /, * or ^, then both values are converted to numbers (see below) and the operation performed.
2. If the operator is OR (and neither of the values is NULL) then both values will be converted to Boolean values, the result of the expression will be 'true' if either expression is true.
3. If the operator is AND then both values will be converted to Boolean values, the result of the expression will be 'true' if both of the expressions are true.
4. If the operator is <, >, >=, <=, <> or = (and not covered by one of rules 1, 2 or 4 above) then the two values will be compared according to the following rules in order and the result will be set to the Boolean value true or false depending on the result.
   1. If either value is a date, then the other value will be converted to a date and a comparison performed.
   2. If either value is a string, then the other value will be converted to a string (see below) and a comparison performed.
   3. Otherwise, both values will be converted to numbers and a comparison performed.
5. If the operator is LIKE or MATCHES, the expression will be false unless both values are strings. If they are strings, the expression will be true or false depending on the result of the appropriate test.

Conversion to strings

1. Boolean values will be converted to the string '1' if true, '0' if false
2. Numbers will be converted to strings using the standard method used throughout InfoAsset Manager, with any trailing zeros after the decimal point removed (e.g. if the number is 12.34000 it will have the final 3 zeros removed and the string will therefore be '12.34'). If there are no non-zero values after the decimal point, the decimal point will also be removed e.g. 123.00000 will have the trailing zeros removed, then will have the decimal point removed, therefore the resulting string will be '123'
3. Dates will be converted to strings using the short form which will be dd/mm/yyyy, mm/dd/yyyy or yyyy/mm/dd depending on how Windows is set up on the PC on which the software is being run. The time is not added in InfoAsset Manager.

Conversion to Boolean

For this purpose, the following are considered to be true:

1. Any string with one or more characters in it
2. Any date
3. Any number other than zero

The following are considered to be false, and therefore if the expression is evaluated for an object and is found to be one of the following, the object will not be selected.

1. NULL
2. Any string with no characters in it
3. The number zero

Conversion to numbers

1. If you assign a value from a date field into a number then the number given will be a floating point number representing the number of days since the 30th December 1899. This is a representation commonly used in Microsoft software.
2. Strings are converted to numbers if the string contains only a number, otherwise the value 0 will be used.
3. If you convert a Boolean field to a number, the value will be 1 if it is true, 0 if it is false.

Unary operators

The unary operators behave as follows:

Unary minus will have the result of –1 * the number if it is a number, NULL otherwise.

NOT will convert the value it applies to to a Boolean (see above) and then invert that value (i.e. if the value it applies to is false the resulting value will be true, and vice versa).

IS NULL will have the result 'true' if the value it applies to is NULL, false otherwise. IS NOT NULL will have the result 'false' if the value it apples to is NULL, true otherwise.

Determining which objects to select based on SQL expressions

When an SQL expression is used to determine which objects to select, all the objects will be selected for which the expression is evaluated and the result considered to be true.

The same criteria will be applied as described in 'conversion to Boolean' above.

Simulation results

These results will appear in the list box for the object type in the SQL dialog prefixed with sim.

It is also possible to load the results of a second simulation by using the appropriate menu item. The results for this simulation will then also appear in the list box for the object type in the SQL dialog prefixed with sim2.

These results will be the results at the current timestep, or the maximum results if the time control has been set to show the maximum results.

It is also possible to use simulation results for all timesteps by using the prefixes tsr and tsr2, in which tsr stands for time series results.

One to one links

It is possible to include values for linked objects in SQL expressions. As described above these fall into two categories, at this stage we are only considering links where an object is linked to ONE other object (or possibly no object) of a particular type. If you are familiar with SQL in databases such as Microsoft Access, Oracle, MySQL etc. you may be familiar with this concept as being a 'join'. However, whereas in these packages you will have to join the table to the other table by means of a view or the JOIN keyword, in InfoAsset Manager, the joining is normally done for you automatically, taking advantage of the software packages knowledge of their own data structures. These are sometimes referred to as 'implicit joins'.

The implicit one-to-one joins within the same InfoAsset Manager Network are as follows:

InfoAsset Manager – Collection Network

From	To	Name
Acoustic Survey	Pipe	pipe
Assets	General Maintenance	general_maintenance_records
	General Survey	general_surveys
	General Survey Line	general_survey_lines
	Tasks	tasks
	User Defined Task Only	user_tasks
	Water Quality Survey	water_quality_surveys
	Work Package	work_packages
Blockage Incident	Pipe	joined
	Property	property
	Node	node
CCTV Survey	Pipe	joined
Channel	Cross Section Survey	cross_section_surveys
Collapse Incident	Node	node
	Pipe	joined
	Property	property
Complaint Incident	Node	node
	Pipe	joined
	Property	property
Cross Section Survey	Channel	channel
Defense Structure	Flood Defence Survey	flood_defence_surveys
Drain Test	Storm Manhole	storm_manhole
	Property	property
	Sanitary Manhole	sanitary_manhole
Dye Test	Pipe	joined
Flood Defence Survey	Defence Structure	defence_structure
Flooding Incident	Node	node
	Pipe	joined
	Property	property
General Incident	Node	node
	Pipe	joined
	Property	property
GPS Survey	Manhole	joined
Link	Node	ds_node
	Node	us_node
Manhole Repair	Manhole	joined
Manhole Survey	Manhole	joined
Materials	Orders	orders
Monitoring Survey	Pipe	joined
	Data Logger	data_logger
	Node	node
Name Group	Node/Pipe/Connection Pipe	asset
Node	Pipe	lateral_pipe
Node/Pipe/Connection Pipe	Name Group	asset_name_groups
Odor Incident	Property	property
	Node	node
	Pipe	joined
Pipe	Acoustic Survey	acoustic_surveys
Pipe Clean	Pipe	joined
Pipe Repair	CCTV Survey	review_cctv_survey
	Pipe	joined
Pipe/Connection Pipe/Pipe Repair	CCTV Survey	cctv_survey
Pollution Incident	Property	property
	Node	node
	Pipe	joined
Property	FOG Inspection	fog_inspections
	Storm Pipe	storm_pipe
	Sanitary Pipe	sanitary_pipe
Pump	Treatment Works	treatment_works
Pump Station	Electrical Maintenance	electrical_maintenance_records
	Generator	generators
	Mechanical Maintenance	mechanical_maintenance_records
	Pump	pumps
	Pump Station Survey	pump_station_surveys
Pump/Generator	Pump Station	pump_station
Smoke Defect	Smoke Test	joined
Smoke Test	Pipe	joined
Tasks	Resource	resources
	Work Package	work_package
Tasks/Incidents	Connection Pipe	connection_pipe
Tasks/Orders	Material	materials
Work Package	Asset	asset
	Tasks	tasks

InfoAsset Manager – Distribution Network

From	To	Name
Assets	General Maintenance	general_maintenance_records
	General Survey	general_surveys
	General Survey Line	general_survey_lines
	Tasks	tasks
	User Defined Task Only	user_tasks
	Water Quality Survey	water_quality_surveys
	Work Package	work_packages
Burst Incident	Pipe	joined
	Node	node
Complaint Incident	Node	node
	Pipe	joined
General Incident	Node	node
	Pipe	joined
GPS Survey	Node	joined
Hydrant	Hydrant Test	hydrant_test
Hydrant Maintenance	Hydrant	hydrant
Hydrant/Meter/Valve Maintenance	Valve Shutoff	valve_shut_off
Link	Node	ds_node
Link	Node	us_node
Manhole Repair	Manhole	joined
Manhole Survey	Manhole	joined
Manhole Survey/Repair	Manhole	manhole
Materials	Orders	orders
Meter	Meter Maintenance	meter_maintenance
	Meter Test	meter_test
Meter Maintenance/Test	Meter	meter
Monitoring Survey	Node	joined
Node	Pipe	lateral_pipe
Node/Pipe	Leak Detection	leak_detections
Pipe Repair	Pipe	joined
Pipe Sample	Pipe	joined
Property	Meter	meter
Pump	Treatment Works	treatment_works
Pump Station	Electrical Maintenance	electrical_maintenance_records
	Generator	generators
	Mechanical Maintenance	mechanical_maintenance_records
	Pump	pumps
	Pump Station Survey	pump_station_surveys
Pump/Generator	Pump Station	pump_station
Tasks	Resource	resources
	Work Package	work_package
Tasks/Orders	Material	materials
Valve	Valve Maintenance	valve_maintenance
	Valve Shutoff	valve_shut_offs
Valve Maintenance	Valve	valve
Valve Shutoff	Hydrant Maintenance	hydrant_maintenance_records
	Meter Maintenance	meter_maintenance_records
	Valve	valves
	Valve Maintenance	valve_maintenance_records
Water Quality Incident	Node	node
	Pipe	joined
Work Package	Asset	asset
	Tasks	tasks

As you can see, the 'joined' prefix has a number of different meanings depending on what is being joined. The 'joined' prefix is used in exactly those circumstances where the fields of the joined object appear in the grids in the software to the right of the object and in italics.