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:

Clear the selection
To clear the selection, use the clause
```
CLEAR SELECTION
```
Set a scalar variable
To set a scalar variable use the LET keyword e.g.
```
LET <variable name> = <value>
```

Set a list variable

To set a list variable use the LIST keyword e.g.

LIST <variable name> = <value 1>, <value 2>, <value 3>, <value n>

Select objects

To select objects use the SELECT keyword e.g.

SELECT

SELECT ALL

SELECT SELECTED

SELECT
 FROM pipe

SELECT ALL 
 FROM pipe

SELECT SELECTED 
 FROM pipe

SELECT 
 WHERE x > 0

SELECT ALL 
 WHERE x > 0

SELECT SELECTED 
 WHERE x > 0

SELECT 
 FROM pipe 
 WHERE diameter > 250

SELECT ALL 
 FROM pipe 
 WHERE diameter > 250

SELECT SELECTED 
 FROM pipe 
 WHERE diameter > 250

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

x > 0

Deselect objects

To deselect objects use the DESELECT keyword

DESELECT

DESELECT ALL

DESELECT SELECTED

DESELECT 
 FROM pipe

DESELECT ALL 
 FROM pipe

DESELECT SELECTED 
 FROM pipe

DESELECT 
 WHERE x > 0

DESELECT ALL 
 WHERE x > 0

DESELECT SELECTED 
 WHERE x > 0

DESELECT 
 FROM pipe 
 WHERE diameter > 250

DESELECT ALL 
 FROM pipe 
 WHERE diameter > 250

DESELECT SELECTED 
 FROM pipe 
 WHERE diameter > 250

Delete objects

To delete objects use the DELETE keyword

DELETE

DELETE ALL

DELETE SELECTED

DELETE 
 FROM pipe

DELETE ALL 
 FROM pipe

DELETE SELECTED 
 FROM pipe

DELETE 
 WHERE x > 0

DELETE ALL 
 WHERE x > 0

DELETE SELECTED 
 WHERE x > 0

DELETE 
 FROM pipe 
 WHERE diameter > 250

DELETE ALL 
 FROM pipe 
 WHERE diameter > 250

DELETE SELECTED 
 FROM pipe 
 WHERE diameter > 250

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 
 WHERE ground_level > 20

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

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

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

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 e.g.

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 using the FROM keyword e.g.

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 FILE keywords e.g.

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 e.g.

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 using the GROUP BY keyword e.g.

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

It is possible to limit the aggregated results reported by using the HAVING keyword e.g.

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 Autodesk 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. Comments can be specified either by line (where the whole line after the comment syntax is treated as comment text) or by block (where a block can specified as part of a line, up to spanning multiple lines). Comment text is syntactically highlighted in green within the SQL dialog.

Syntax	Description
`//`	Line comment
`/*`	Block comment - Start of comment text block
`*/`	Block comment - End of comment text block

Functions

Syntax	Description
`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`. For example: 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' Note: 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. Tip: Regular expressions are powerful and you can do far more with them than the example provided here. For more information on regular expressions and their syntax, see this article on Wikipedia.
`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)`	`YEARPART` is applied to / returns the year of a date. If date is 24/09/2024 (assuming DD/MM/YYYY formatting), `YEARPART` is 2024. Note: The `YEARPART`, `MONTHPART`, and `DAYPART` 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.
`MONTHPART(s)`	`MONTHPART` is applied to / returns the month of a date. `MONTHPART` is 09 if we take the example above. Note: The `YEARPART`, `MONTHPART`, and `DAYPART` 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.
`DAYPART(s)`	`DAYPART` is applied to / returns the day of a date. In the above example, `DAYPART` is 24. Note: The `YEARPART`, `MONTHPART`, and `DAYPART` 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.
`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(date)`	`TIMEPART` returns the time part of a date as a number of minutes after midnight.
`YEARSDIFF(from,to)`	`YEARSDIFF` returns the number of complete years between two dates. Note: The `YEARSDIFF`, `MONTHSDIFF`, and `DAYSDIFF` functions ignore any minutes part of the dates in question.
`MONTHSDIFF(from,to)`	`MONTHSDIFF` returns the number of complete months between two dates. Note: The `YEARSDIFF`, `MONTHSDIFF`, and `DAYSDIFF` functions ignore any minutes part of the dates in question.
`DAYSDIFF(from,to)`	`DAYSDIFF` returns the number of complete days between two dates. Note: The `YEARSDIFF`, `MONTHSDIFF`, and `DAYSDIFF` functions ignore any minutes part of the dates in question.
`INYEAR(date,number)`	`INYEAR` returns true if the date is in the year given as a number. Note: If any of the parameters to the `INYEAR`, `INMONTH`, `INYEARS`, and `INMONTHS` 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)`	`INMONTH` returns true if the date is in the year and month given as numbers. Note: If any of the parameters to the `INYEAR`, `INMONTH`, `INYEARS`, and `INMONTHS` functions are not numbers the function will return false. If the number is not an integer, the number is rounded to the nearest integer.
`INYEARS(date,startyear,endyear)`	`INYEARS` returns true if the date is in a year between the start and end years inclusive. Note: If any of the parameters to the `INYEAR`, `INMONTH`, `INYEARS`, and `INMONTHS` functions are not numbers the function will return false. If the number is not an integer, the number is rounded to the nearest integer.
`INMONTHS(date,startmonth,startyear,endmonth,endyear)`	`INMONTHS` returns true if the date is between the start month in the start year and the end month in the end year inclusive. Note: If any of the parameters to the `INYEAR`, `INMONTH`, `INYEARS`, and `INMONTHS` functions are not numbers the function will return false. If the number is not an integer, the number is rounded to the nearest integer.
`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 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

The purpose of these functions is to divide values into ranges, score values, map values onto lists etc.

Syntax	Description
`AREF(n,list)`	`AREF`, given a list variable `list` and a number from 1 to the length of `list`, returns the `n`^th element in the list.
`LEN(list variable)`	`LEN` returns the number of items in a list variable.
`RINDEX(expression, 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’.
`LOOKUP(expression, list variable)`	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(expression, list variable)`	`MEMBER` returns true if the value of the expression is one of the values in the list, false otherwise.
`INDEX(expression, list variable)`	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(n,list)`	`TITLE` provide titles for the 'buckets' when `RINDEX` is used to partition values into a number of ranges.

Mathematical functions

Syntax	Description
`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

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 type being queried in a particular fashion
all the timesteps in the simulation results

Syntax	Description
`ANY(conditional expression)`	`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. Note: 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(conditional expression)`	`ALL` returns true if the expression is true for all the rows / related objects / timesteps.
`COUNT(conditional expression)`	`COUNT` counts the number of rows / related objects / timesteps for which the expression is true.
`MAX(conditional expression)`	`MAX` returns the maximum value (number, date or string) for all the rows / related objects / timesteps. Note: `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.
`MIN(conditional expression)`	`MIN` return the minimum value (number, date or string) for all the rows / related objects / timesteps. Note: `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(conditional expression)`	`AVG` returns the mean of all non-null values of the expression (numerical only) for all the rows / related objects / timesteps.
`FIRST(conditional expression)`	`FIRST` returns the value of the expression for the first row / related object / timestep.
`LAST(conditional expression)`	`LAST` returns the value of the expression for the last row / related object / timestep. Thus, in calculating the value, one of the records in the array field is considered.

Arithmetic operators

Syntax	Description
`+`	All of these operators do what you would expect for numbers. `<>` means "not equal to" or "does not equal", depending on the context. 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.
`%`	The `%` operator means "the modulus of", so `a % b` gives the remainder of a / b e.g. year % 10 gives the last digit of the year.

Logical operators

Syntax	Description
`AND`	Self-explanatory
`OR`
`NOT`

Other operators

Syntax	Description
`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

Syntax	Description
TRUE	Self-explanatory
FALSE
NULL

Aggregate functions (results only)

It is possible to use the aggregate functions over all simulation results instead of just one snapshot by using tsr instead of sim.

Syntax	Description
`DURATION(conditional expression)`	The time (in minutes) for which the expression is true (results only) e.g. DURATION(tsr.pressure < `x`) will return the cumulative time (in minutes) a node has a pressure below `x`.
`INTEGRAL(conditional expression)`	The sum of the expression at each timestep multiplied by the length of the timestep in minutes (results only) e.g. INTEGRAL(tsr.flow*60) will return the total volume of water that's flowed through a pipe (in litres), assuming l/s is being used as the flow unit.
`WHENEARLIEST(conditional expression)`	The earliest time for which the expression is true (results only) e.g. WHENEARLIEST(tsr.pressure < `x`) will return the Date Time of the earliest timestep a node has a pressure below `x`.
`WHENLATEST(conditional expression)`	The latest time for which the expression is true (results only) e.g WHENLATEST(tsr.pressure < `x`) will return the Date Time of the last timestep a node has a pressure below `x`.
`EARLIEST(conditional expression)`	The first non-null value of the expression (results only). This is only likely to give a meaningful answer if used in combination with the `IIF` function e.g. EARLIEST(IIF(tsr.pressure < `x`, tsr.pressure, NULL)) will return a node's pressure at the earliest timestep that node had a pressure below `x`.
`LATEST(conditional expression)`	The last non-null value of the expression (results only). This is only likely to give a meaningful answer if used in combination with the `IIF` function e.g. LATEST(IIF(tsr.pressure < `x`, tsr.pressure, NULL)) will return a node's pressure at the latest timestep that node had a pressure below `x`.
`WHENMAX(conditional expression)`	The time at which the expression is at its maximum (results only) e.g. WHENMAX(tsr.pressure) will return the Date Time a node hits its recorded maximum pressure.
`WHENMIN(conditional expression)`	The time at which the expression is at its minimum (results only) e.g. WHENMIN(tsr.pressure) will return the Date Time a node hits its recorded minimum pressure.

Additional notes on using SQL

The SQL engine does not do much in the way of type checking so you can do a number of potentially useful things, such as:
1. ```
SET user_number_1 = x > 390000
```
  will set user_number_1 to 1 for things with x > 390000, 0 for the others, as conditional expressions evaluate to 1 for true and 0 for false.
2. ```
SET user_text_1 = z
```
  will set user_text_1 to the z value, note that when you do this, the strings have unnecessary decimal points and decimal places trimmed off, this is so you can take these strings and concatenate other stuff on the end if you like.
3. ```
SET user_number_1 = user_text_1
```
  will assign the value if the string is a valid number.
The behaviour of NULL is slightly complex.
1. Essentially NULL is an 'I have no value' value - the grid views work the same way for numbers in that there is a difference between putting 0 in a grid cell for a number and leaving it blank. The 'blank' value is NULL, i.e. the objects with blank cells are the ones that are selected if you do x IS NULL.
2. The general rule for SQL is that any operation involving anything and NULL gives the answer NULL e.g.
  - 3 + NULL is NULL
  - 3 > NULL is NULL
  - 3 < NULL is NULL
  The SQL engine treats equality slightly differently. x = NULL and x <> NULL do the same as X IS NULL and X IS NOT NULL.
For strings, an empty string is simultaneously NULL and "" (empty string). Therefore
- ```
asset_id IS NULL
```
- ```
asset_id = ""
```
- ```
LEN(asset_id)=0
```
all select things with no asset ID string.
Boolean fields (fields with check boxes) do not have any concept of NULL.
You may encounter a parsing error when you have included a field name which starts with a number. It is recommended that you place such field name between [ ] in your SQL query.
For example, instead of writing
```
SET 2d_pt_id = subcatchment_id
```
you can use
```
SET [2d_pt_id] = subcatchment_id
```