SQL syntax

Notes on keywords

An SQL block can contain a number of clauses separated by semicolons.

The individual blocks can be used to perform the following tasks using the following keywords:

Clear the selection – CLEAR SELECTION
Set a scalar variable - LET
Set a list variable - LIST
Select objects - SELECT
Deselect objects (i.e. remove objects from the selection) - DESELECT
Delete objects - DELETE
Change the values of fields or variables - SET and UPDATE
Generate a table of values which can be displayed in a grid or output to a CSV file – SELECT

With the exception of the first 3 of these:

The current table can be overridden by using the FROM keyword
The current selection mode (i.e. the state of the ‘apply filter to current selection’ check-box) can be overridden by using the ALL or SELECTED keywords
The objects to which the action is applied can be filtered by using the WHERE keyword.

Let us now give more information on the keywords:

1. Clear the selection

To clear the selection, use the clause

CLEAR SELECTION

2. Set a scalar variable

To set a scalar variable, say

LET <variablename> = <value>

3. Set a list variable

To set a list variable, say

LIST <variablename> = <value>, <value>, <value>, <value>

4. Select objects

To select objects use the SELECT keyword e.g.

SELECT

SELECT ALL

SELECT SELECTED

SELECT FROM conduit

SELECT ALL FROM conduit

SELECT SELECTED FROM conduit

SELECT WHERE x > 0

SELECT ALL WHERE x > 0

SELECT SELECTED WHERE x > 0

SELECT FROM conduit WHERE conduit_width > 250

SELECT ALL FROM conduit WHERE conduit_width > 250

SELECT SELECTED FROM conduit WHERE conduit_width > 250

If you are selecting from the current table with the current selection mode, the SELECT keyword can be omitted e.g.

x>0

5. Deselect objects

To deselect objects use the DESELECT keyword

DESELECT

DESELECT ALL

DESELECT SELECTED

DESELECT FROM conduit

DESELECT ALL FROM conduit

DESELECT SELECTED FROM conduit

DESELECT WHERE x > 0

DESELECT ALL WHERE x > 0

DESELECT SELECTED WHERE x > 0

DESELECT FROM conduit WHERE conduit_width > 250

DESELECT ALL FROM conduit WHERE conduit_width > 250

DESELECT SELECTED FROM conduit WHERE conduit_width > 250

6. Delete objects

To delete objects use the DELETE keyword

DELETE

DELETE ALL

DELETE SELECTED

DELETE FROM conduit

DELETE ALL FROM conduit

DELETE SELECTED FROM conduit

DELETE WHERE x > 0

DELETE ALL WHERE x > 0

DELETE SELECTED WHERE x > 0

DELETE FROM conduit WHERE conduit_width > 250

DELETE ALL FROM conduit WHERE conduit_width > 250

DELETE SELECTED FROM conduit WHERE conduit_width > 250

7. Change the value of fields or variables

To change the value of fields or variables use the SET keyword. It is possible to set more than one value at a time by separating the assignments with commas.

SET x = x – 100, y = y – 100

SET x = x – 100, y = y – 100 WHERE ground_level > 20

To override the current table and current selection mode the UPDATE keyword should be used

UPDATE ALL SET x = x – 100, y = y - 100

UPDATE SELECTED SET x = x – 100, y = y - 100

UPDATE node SET x = x – 100, y = y - 100

UPDATE ALL node SET x = x – 100, y = y - 100

UPDATE SELECTED node SET x = x – 100, y = y – 100

UPDATE ALL SET x = x – 100, y = y – 100 WHERE ground_level > 20

UPDATE SELECTED SET x = x – 100, y = y – 100 100 WHERE ground_level > 20

UPDATE node SET x = x – 100, y = y - 100100 WHERE ground_level > 20

UPDATE ALL node SET x = x – 100, y = y – 100 100 WHERE ground_level > 20

UPDATE SELECTED node SET x = x – 100, y = y – 100 100 WHERE ground_level > 20

8. Generate a table of values

To select a number of values use the SELECT keyword then follow it with the values you wish to output separated by commas.

SELECT node_id ,MAX(us_links.conduit_width), MIN(us_links.conduit_width)

It is possible to override the title given to the column in the grid or CSV file using the keyword AS e.g.

SELECT node_id AS title,MAX(us_links.conduit_width) as mymax,MIN(us_links.conduit_width) as mymin

The title can either be without quotes round it in which case there can be no spaces or non-alphanumeric characters or in quotes e.g.

SELECT node_id AS title,MAX(us_links.conduit_width) as mymax,MIN(us_links.conduit_width) as '£% my min'

It is possible to override the table

SELECT node_id AS title,MAX(us_links.conduit_width) as mymax,MIN(us_links.conduit_width) as mymin FROM node

The results will be displayed in a grid unless a file is specified using the INTO keyword

SELECT node_id AS title,MAX(us_links.conduit_width) as mymax,MIN(us_links.conduit_width) as mymin FROM node INTO FILE 'c:\temp\mynodes.csv'

It is possible to filter the objects that will be reported on using the WHERE keyword

SELECT node_id AS title,MAX(us_links.conduit_width) as mymax,MIN(us_links.conduit_width) as mymin FROM node WHERE MAX(us_links.conduit_width) > 450

It is possible to calculate results aggregated over groups of objects

SELECT MAX(MAX(us_links.conduit_width)) GROUP BY system_type

It is possible to limit the aggregates results reported by using the HAVING keyword

SELECT MAX(MAX(us_links.conduit_width)) GROUP BY system_type HAVING MAX(MAX(us_links.conduit_width))<1000

Supported SQL syntax

The table below lists the SQL syntax supported in this Innovyze product. Additional notes can be found below.

You can refer to the SQL in InfoWorks WS Pro technical paper for comprehensive information on the use of SQL and all the functions supported.

Comment text

Comment text is ignored when the query is executed.

/*	Start of comment text
*/	End of comment text

Functions

INT(number)	Integer part of a number.
FLOOR(number)	Closest integer less than or equal to the parameter.
CEIL(number)	Closest integer greater or equal to its parameter.
FIXED(number to convert,number of decimal places)	Given a number and a number of decimal places from 0 to 8, convert the number to a string with that number of decimal places, rounding up or down as appropriate. If the number of decimal places is 0 the string will have no decimal point.
abs(x)	Takes the absolute value of x. The function returns x if x >= 0, -x otherwise
left(s,n)	Returns the first n characters of the string s, or the whole string if n is greater than the length of the string
right(s,n)	Returns the last n characters of the string s, or the whole string if n is greater than the length of the string)
mid(s,n,m)	Returns the substring of string s containing m characters starting at position n, counting from 1 as the start position
len(s)	Returns the length of the string s
iif(x,y,z)	If the expression x is true then returns y otherwise returns z
nvl(x,y)	If x is null then returns y otherwise returns x
subst(s1,s2,s3)	Replaces the first instance of string s2 in string s1 with string s3 For example: subst(node_id,'01','ND') applied to node ID '01880132', returns 'ND880132'
gsubst(s1,s2,s3)	Replaces all instances of string s2 in string s1 with string s3 For example: gsubst(node_id,'01','ND') applied to node ID '01880132', returns 'ND88ND32'
gensubst(s,regexp,format)	Replaces string s with the format defined if regular expression regxp matches string s, otherwise returns string s unchanged. For example: gensubst(node_id,'(01)(..)(..)(.)','ND\2\4a\1') applied to node '01880132', returns 'ND8832a01' The regular expression can be used to divide the string into subexpressions by bracketing. In the example above, '(01)(..)(..)(.)' matches any node ID of at least 6 characters beginning with 01. The first (..) corresponds with the 3rd and 4th characters of the string, the second (..) the 5th and 6th and (.*) the rest of the string. If a match is made, a string is returned in the defined format; where \1 represents the values in the first bracketed subexpression '01', \2 represents the values in the second bracketed subexpression (..) - '88' in this case, etc. up to \9.
nl()	Returns a new-line character For example, to set up a three line note field: SET notes = 'Set'+NL()+'new'+NL()+'line'
yearpart(s)	These three functions work on date fields and string fields which are formatted as dates. A zero is returned if the string they are applied to is not a date. YEARPART is applied to / returns the year of a date. If date is 24/09/2007, YEARPART is 2007. MONTHPART is applied to / returns the month of a date. MONTHPART is 09 if we take the example above. DAYPART is applied to / returns the day of a date. In the above example, DAYPART is 24.
monthpart(s)
daypart(s)
NOW()	Function without any parameter defining the present. This function looks at the current date and time of the computer, so that SQL queries with NOW do not have to be rewritten every time before they are executed to make them current.
DATEPART(date)	DATEPART returns the date part as a date (e.g. removes any minutes from the dates). TIMEPART returns the time part of a date as a number of minutes after midnight.
TIMEPART(date)
YEARSDIFF(from,to)	YEARSDIFF returns the number of complete years between two dates. MONTHSDIFF returns the number of complete months between two dates. DAYSDIFF returns the number of complete days between two dates. Please note that these three functions ignore any minutes part of the dates in question.
MONTHSDIFF(from,to)
DAYSDIFF(from,to)
INYEAR(date,number)	INYEAR returns true if the date is in the year given as a number INMONTH returns true if the date is in the year and month given as numbers. INYEARS returns true if the date is in a year between the start and end years inclusive. INMONTHS returns true if the date is between the start month in the start year and the end month in the end year inclusive Please note that if any of the parameters to these functions are not numbers the function will return false. If the number is not an integer, the number is rounded to the nearest integer.
INMONTH(date,month,year)
INYEARS(date,startyear,endyear)
INMONTHS(date,startmonth,startyear,endmonth,endyear)
ISDATE(putative_date)	If the field is a date because it has come from the database returns true, if it is a string then returns true if it can be converted into a date, otherwise returns false.
MONTHYEARPART(date)	MONTHYEARPART returns the string "<month>/<year>".
YEARMONTHPART(date)	YEARMONTHPART returns the string "<year>/<date>"
MONTHNAME(date)	MONTHNAME returns returns the name of the month (in the current locale).
SHORTMONTHAME(date)	SHORTMONTHNAME returns the abbreviated name of the month (as determined by the locale and how Windows abbreviates it).
DAYNAME(date)	DAYNAME returns the name of the day (in the current locale).
SHORTDAYNAME(date)	SHORTDAYNAME returns the abbreviated day name (as determined by the locale and how Windows abbreviates it).
NUMTOMONTHNAME(n)	Returns the month name given an integer from 1 to 12.
NUMTOSHORTMONTHNAME(n)	Returns the short version of a month name (e.g. JAN) given an integer from 1 to 12.
TODATE(year,month,day)	Returns the date given the year, month and day as integers.
TODATETIME(year,month,day,hours,minutes)	Returns the date given the year, month and day as integers.

List functions

AREF(n,list)	The purpose of these functions is to divide values into ranges, score values, map values onto lists etc. AREF, given a list variable list and a number from 1 to the length of list, returns the nth element in the list. LEN returns the number of items in a list variable. RINDEX is a function that may only be used if the list is sorted. The purpose of the RINDEX function is essentially to divide values into ‘buckets’. If there are n items in the list and the value of the expression is between 1 and n inclusive, LOOKUP will return the appropriate item from the list. MEMBER returns true if the value of the expression is one of the values in the list, false otherwise. If the result of the expression is the first value in the list, INDEX will return 1, if it is the value of the 2nd it will return 2, if the result of the expression is not the list this will return 1. TITLE provide titles for the 'buckets' when RINDEX is used to partition values into a number of ranges.
LEN(list variable)
RINDEX(expression, list variable)
LOOKUP(expression, list variable)
MEMBER(expression, list variable)
INDEX(expression, list variable)
TITLE(n,list)

Mathematical functions

LOG(x)	Calculates the log (base 10) of x.
LOGE(x)	Calculates the log (base e) of x.
EXP(x)	Calculates e^x.
SIN(x)	Calculates the sin of x.
COS(x)	Calculates the cosine of x.
TAN(x)	Calculates the tangent of x.
ASIN(x)	Calculates the inverse sin of x.
ACOS(x)	Calculates the inverse cosine of x.
ATAN(x)	Calculates the inverse tangent of x.
ATAN2(x,y)	Calculates the inverse tangent of x / y using the signs of x and y to determine the quadrant.
GAMMALN(x)	Returns the LOG (base e) of the Gamma function of x.

Aggregate functions

ANY	Aggregate functions are used to allow the calculation of values based on: all the rows of an array field all the objects related to the object being queried in a particular fashion all the timesteps in the simulation results ANY returns true if the expression is true for any row of the array field e.g. ANY(details.code=’JDS’) will return true if any of the rows of the details array field has the code JDS, false otherwise. The expression within the brackets can contain more than one array field, other fields of the object, constants and non-aggregate functions, and these can all be combined with arithmetic, comparison and logical operators. ALL returns true if the expression is true for all the rows / related objects / timesteps. COUNT counts the number of rows / related objects / timesteps for which the expression is true. MAX returns the maximum value (number, date or string) for all the rows / related objects / timesteps. MIN return the minimum value (number, date or string) for all the rows / related objects / timesteps. MAX and MIN both work on numerical, date and string fields. In the case of string fields the comparison between strings is performed based on the language in which your Windows installation is set up. AVG returns the mean of all non-null values of the expression (numerical only) for all the rows / related objects / timesteps. FIRST returns the value of the expression for the first row / related object / timestep. LAST returns the value of the expression for the first row / related object / timestep. Thus, in calculating the value, one of the records in the array field is considered.
ALL
COUNT
MAX
MIN
AVG
FIRST
LAST

Arithmetic operators

+
-
*
/
>
<
=
>=
<=
<>

All of these operators do what you would expect for numbers. <> means "not equal to".

In addition, + can be used to concatenate strings. + is the only arithmetic operator that has a special meaning for strings.

The ^ operator means "raise to the power". For example x^2 means x squared.

a % b gives the remainder of a / b e.g. year % 10 gives the last digit of the year.

Logical operators

AND
OR
NOT

Which will do what you expect

Other operators

IS NULL	Evaluates to true if a field is NULL
IS NOT NULL	Evaluates to true if a field is not NULL
LIKE	The two special characters you can use with LIKE are: * meaning match anything ? meaning match one character Every other character in a string matches only itself. The matching is DOS like i.e.: LIKE '' matches anything LIKE 'ABC' matches anything beginning with ABC LIKE '???' matches any 3 character string As with DOS, once you hit a * everything is matched. Therefore you can't do things like LIKE 'AB' to find things with AB at the end, or LIKE 'AB*' to find something with AB somewhere in the string. Note that this behaviour of LIKE is different from that in Microsoft Access.
MATCHES	MATCHES allows you to carry out more complex searches than the LIKE operator by using regular expressions. The important syntax elements are: . - (dot) any alphanumeric character * - zero or more of the previous character. So B* will match zero or more Bs + - one or more of the previous character. So B+ will match one or more Bs ? - zero or one of the previous character. So B? will match zero or one B The matching works as follows: MATCHES '.' matches anything MATCHES 'ABC.' matches any string beginning with ABC MATCHES 'ABC.+' matches any string beginning with ABC followed by at least one more character MATCHES '.ABC.' matches any string containing ABC MATCHES '.ABC' matches any string ending with ABC MATCHES 'RS' matches any string beginning with R followed by zero or more Ss Regular expressions are powerful and you can do far more with them than the basic examples shown above. For more information on regular expressions and their syntax, see this article on Wikipedia.

Constants

True

False

NULL

Self-explanatory

Aggregate functions (results only)

DURATION	It is possible to use the aggregate functions over all simulation results instead of just one snapshot by using tsr instead of sim. DURATION - the time (in minutes) for which the expression is true (results only) INTEGRAL - the sum of the expression at each timestep multiplied by the length of the timestep in minutes (results only) WHENEARLIEST - the earliest time for which the expression is true (results only) WHENLATEST - the latest time for which the expression is true (results only) EARLIEST - the first non-null value of the expression (results only) LATEST - the last non-null value of the expression (results only) WHENMAX - the time at which the expression is at its maximum (results only) WHENMIN - the time at which the expression is at its minimum (results only)
INTEGRAL
WHENEARLIEST
WHENLATEST
EARLIEST
LATEST
WHENMAX
WHENMIN

SQL and stored queries