SQL Syntax

Refer to the SQL in InfoWorks ICM technical paper for comprehensive information on the use of SQL and all the functions supported.

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:

  1. Clear the selection – CLEAR SELECTION
  2. Set a scalar variable - LET
  3. Set a list variable - LIST
  4. Select objects - SELECT
  5. Deselect objects (i.e. remove objects from the selection) - DESELECT
  6. Delete objects - DELETE
  7. Change the values of fields or variables - SET and UPDATE
  8. 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 use a blob field in a GROUP BY query

SELECT SUM(COUNT(details.*)) GROUP BY details.code

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

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

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:

Closed

gsubst(s1,s2,s3)

Replaces all instances of string s2 in string s1 with string s3

For example:

Closed

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:

Closed

  • 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)

yearpart(s), monthpart(s) and daypart(s) 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(s)

MONTHPART is applied to / returns the month of a date. MONTHPART is 09 if we take the example above.
DAYPART(s) DAYPART is applied to / returns the day of a date. In the above example, DAYPART is 24.

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.
YEARMONTHPART(date) YEARMONTHPART returns the string "<year>/<date>" e.g. "2001/01". If the month is January - September a 0 prefix is applied.
MONTHYEARPART(date) MONTHYEARPART returns the string "<month>/<year>" e.g. "01/2010". if the month is January - September, a 0 prefix is applied.
MONTHNAME(date) MONTHNAME returns the name of the month (in the current locale)
SHORTMONTHNAME(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)

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.

YEARSDIFF(from,to)

YEARSDIFF returns the number of complete years between two dates.

Please note that this functions ignores any minutes part of the dates in question.

MONTHSDIFF(from,to)

MONTHSDIFF returns the number of complete months between two dates.

Note that this functions ignores any minutes part of the dates in question.

DAYSDIFF(from,to)

DAYSDIFF returns the number of complete days between two dates.

Note that this functions ignores any minutes part of the dates in question.
INYEAR(date, number)

Returns true if the date is in the year given as a number e.g.

INYEAR(when_surveyed,1993)

Note that if any of the parameters to this function is not a number the function returns false.

If the number is not an integer, the number is rounded to the nearest integer.
INMONTH(date,month,year)

Returns true if the date is in the year rand month given as numbers e.g.

INMONTH(when_surveyed,3,1993)

Note that if any of the parameters to this function is not a number the function returns false.

If the number is not an integer, the number is rounded to the nearest integer.
INYEARS(date,startyear,endyear)

Returns true if the date is in a year between the start and end years inclusive e.g.

INYEARS(when_surveyed,1993,1995)

Note that if any of the parameters to this function is not a number the function returns false.

If the number is not an integer, the number is rounded to the nearest integer.
INMONTHS(date,startmonth,startyear,endmonth,endyear)

Returns true if the date is between the start month in the start year and the end month in the end year inclusive e.g.

INMONTHS(whensurveyed,10,1993,2,1994)

Note that if any of the parameters to this function is not a number the function returns 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.
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, day, hour and minute as integers.
DAYOFWEEK(date) Given a date, returns the day-of-the-week of the date as a number according to the ISO8601 standard (from 1=Monday to 7=Sunday)
DAYOFYEAR(date) Given a date, returns the day-of-the-year of the date as a number (1=January 1st)
DAYSINYEAR(date) Given a date, returns the number of days in the year of the date

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

Works 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

Returns the minimum value (number, date or string) for all the rows / related objects / timesteps

Works 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.

When querying time series results the length of timesteps can vary. Therefore AVG returns time weighted average i.e. the sum of the values for all timesteps except the last multiplied by the duration of that timestep, divided by the total duration.

FIRST

 Returns the value of the expression for the first row / related object / timestep.

LAST

 Returns the value of the expression for the last row / related object / timestep.
SUM

Applicable to time series results.

Returns the sum of all the values at all timesteps.
DURATION

Applicable to time series results.

Returns the time in minutes for which the expression is true for timesteps under consideration.

Timesteps are not necessarily contiguous.
EARLIEST

Applicable to time series results.

Returns the first non-null value of an expression.

This is only likely to give a meaningful answer if used in combination with the IIF funtion e.g. to find the first time at which the depth is greater than 0.01:

SELECT EARLIEST(IIF(tsr.depth>0.01,tsr.depth,NULL)

LATEST

Applicable to time series results.

Returns the last non-null value of an expression.
INTEGRAL

Applicable to time series results.

Returns the sum of the value of the expression at each timestep multiplied by the length of the timestep in minutes.
WHENEARLIEST

Applicable to time series results.

Returns the earlies time for which the expression is true.
WHENLATEST

Applicable to time series results.

Returns the latest time for which the expression is true.

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

LEN(list variable)

LEN returns the number of items in a list variable.

RINDEX(expression, list variable)

May only be used if the list is sorted.

Returns 0 if the result of the expression is less than the first value in the list, 1 if it greater than or equal to the first value in the list but less than the second value in the list, 2 if it is greater than or equal to the second value in the list but less than the third value etc.

If there are n items in the list and the result of the expression is greater than or equal to the final item in the list then RINDEX will return n.

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.

LOOKUPFN (expression, clamp option, step option, list variable)

Implements a stepwise or piecewise-linear x->y lookup function that takes a NULL or numeric input expression (x) and returns a NULL or numeric value (y).

The list variable must be a number list containing a sequence of x,y pairs in monotonic ascending order of x, with a minimum of one such pair.

If step option is true, the function is evaluated stepwise, otherwise it is evaluated as piecewise linear.

If x is NULL, y is NULL.

If the clamp option is true, x is clamped to the range of the function before evaluating y. Otherwise if x is outside the range of the function, y is NULL.

For example:

LIST $fn = 0,0, 10,100;

SET $x = 5;

SET $y = LOOKUPFN($x, false, false, $fn);

y should be 50

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.
AREF(n,list)

Given a list variable list and a number from 1 to the length of list returns the nth element in the list e.g.

LIST $a = 'one','two','three'

AREF(2,$a) will return 'two'

Trigonometric functions are functions of an angle.

All these functions return NULL if their parameters cannot be converted into numbers. There may be further restrictions on the parameters as described below.

Please note that all angles in the trigonometric functions are expressed in degrees.

ACOS(x)

Calculates the inverse cosine of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range 0 to 180 degrees.

ASIN(x)

Calculates the inverse sin of x. Returns NULL if x is less than -1 or greater than 1. Returns a value within the range -90 degrees to 90 degrees.

ATAN(x)

Calculates the inverse tangent of x. Returns a value within the range -90 degrees to 90 degrees.

ATAN2(y,x)

Calculates the inverse tangent of x / y using the signs of x and y to correctly determine the quadrant. Returns a value within the range -180 degrees to 180 degrees.

COS(x)

Calculates the cosine of x.

EXP(x)

Calculates e^x.
GAMMALN(x) Calculates the natural logarithm of the gamma function, Γ(x). Returns NULL if x<=0.

LOG(x)

Calculates the log (base 10) of x. Returns NULL if x cannot be converted into a number, or x<=0.

LOGE(x)

 Calculates the log (base e) of x, otherwise known as a natural logarithm. Returns NULL if x<=0.

SIN(x)

Calculates the sin of x.

TAN(x)

 Calculates the tangent of x. Returns NULL if cos(x) = 0 (and therefore tan(x) would be infinite).

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

Self-explanatory.

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

Constants

True

False

NULL

Self-explanatory
  1. 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.
  2. The behaviour of NULL is slightly complex.
    1. Essentially NULL is an 'I have no value' value - the Grid Windows 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.

  3. For strings, an empty string is simultaneously NULL and "'' (empty string). Therefore asset_id IS NULL and asset_id = ''" and len(asset_id)=0 all select things with no asset ID string.
  4. Boolean fields (fields with check boxes) do not have any concept of NULL.

SQL Queries

SQL Dialog

SQL in InfoWorks ICM