TVD Connector  SQL Expression Dialog
This dialog is a simplified version of the standard Innovyze SQL dialog and it enables you build an SQL expression for a TVD connector.
The expression can be checked using the Test button in the dialog. If the expression is valid, the results from evaluating the expression are displayed; but if not, a suitable warning will be issued.
A saved expression is automatically evaluated during an appropriate Run.
The SQL Expression dialog is displayed when you click in the Output expression field in the TVD Connector Grid Window of the Polygons Grid or a TVD Connector's Property Sheet.
If you are familiar with SQL syntax, you can type in an expression manually (using the Field dropdown list, if required, to insert the fields you want to include in the expression). The supported SQL operators and functions, which can be used in an expression, are listed below.
Alternatively, click Builder >> . This displays a set of buttons representing the operators that you can include in the expression.
Field 
Description 

Object Type 
A readonly field displaying the type of object to which this query will apply. This is always a TVD connector. 
Field Type 
A readonly field which shows the fields related to the TVD connector in the Field dropdown list. 
Field 
A list of database fields applicable to the TVD connector can be selected from the dropdown list. This list includes all the fields that comprise the TVD connector (as displayed in its grid window or property sheet) as well as the following fields:
Note
Currently, only fields which can return a numerical value are included in the evaluation of an expression. Selecting a field from the list inserts the field into the expression. 
Display precision 
Select the number of decimal places to be shown when displaying the results of the SQL expression. 
Builder 
Click the Builder >> button to display a set of buttons representing the operators that can be included in the expression. Click the Finish << button to close the builder. 
Test 
Tests that the expression contains valid SQL functions and operators. See Testing the expression for further information. 
Save 
Saves the current expression. The saved expression is displayed in the Output expression field in the TVD Connector's property sheet or grid window. 
Close 
Closes the SQL Expression dialog without saving the expression. 
Supported SQL syntax for TVD expressions
TVD expressions are used to create and manipulate whole time series of numeric (or null) data. All time series exist in a common time domain defined by start and end times (usually based on the run start and end time) and each time series has an interpolation rule (linear or step/extend) that determines how values can be interpolated at times that fall between the individual data points in the series. See the technical paper Theory of time series expressions for more about how time series are represented and manipulated.
Any of the standard set of arithmetic, logical or comparison operators, unary or binary, may be applied to a time series data value or pair of values. All of the standard SQL functions that take one or more numeric arguments and that yield a numeric result can also be used with time series data arguments and will yield a time series data value as a result. See the topic SQL Syntax for more details.
Specialised functions for use with time series data values
The following functions can be used only to create or manipulate time series data values. They cannot be used in "normal" SQL expressions and queries. These functions are described in more detail in the technical paper Theory of time series expressions.
The following functions and operators can be used in TVD expressions:
Creates a time series from a data source that is either a single number, or a stream of observed or previously calculated data.
Arguments 
Description 

source 
One of the three input fields (input_a, input_b and input_c) is identified as a source of time series data, where the input fields can be either a numeric value or a string which must be one of:

units  Must be specified as a string such as "mm" or "ft", and are used only if the input field is a TSDB stream id, in which case the specified units need to be compatible with the units (quantity) of the input, or if the input field is a number. No units should be specified using quotes, e.g. "". 
Generate a new timeseries with specified intervals.
Arguments 
Description 

interval_unit  Must be one of the following:

interval_multiplier  The repeating time interval. For example, this would be 15 if the repeating time interval was every 15 minutes. 
date_origin  A string representing the date in the format yyyymmdd 
time_of_day_origin  A string representing the time in the format hh:mm, hh:mm:ss or hh:mm:ss.1 
daylight_saving  Should be either1 (true) or 0 (false). 
Apply smoothing and statistical resampling.
Resamples the values in value_series using the time stamps of the nonnull, nonzero data points in time_stamp_series, calculating the value for a window around each time stamp using the specified statistic and window_option.
A resampling buffer (set on a tvd connector) can be defined to extend the time domain backwards in chained simulations.
Arguments 
Description 

value_series  Time series data generated by TSDATA 
time_stamp_series  A time series generated by TSPOINTS 
statistic 
One of the following:

window_option 
1 – window is the interval between this time stamp and the next time stamp 0 – window is from left_secs seconds before this time stamp to right_secs seconds after it 1 – window is the interval between the preceding time stamp and this time stamp 
left_secs  Used to define the width of the left part of the time window in seconds 
right_secs  Used to define the width of the right part of the time window in seconds 
statistic_factor  A multiplier applied after calculating the statistic 
Output_interpolation  Linear, step or extend 
Apply a repeating or individual (event) profile constructed from a list of numbers.
Arguments 
Description 

input_series  A time series generated by TSPOINTS 
normalize  1 (true) or 0 (false) 
divide_by_duration  1 (true) or 0 (false). If this is true then the initial values are divided by the duration of the interval. 
multiplier  Each value can be multiplied by the specified multiplier. This can be used for unit conversion. 
output_interpolation  Linear, step or extend 
number_list  A list of numerical values 
Constructs a series containing the same data points as the input series, but with time stamps shifted forward (lagged) by the specified number of seconds.
Arguments 
Description 

input_series  A time series generated by TSPOINTS 
lag seconds 
The number of seconds by which the timesteps are to lag. A negative lag will shift the time stamps backwards. The resampling buffer for the TVD connector (in minutes) must be set to be at least as large as the lag time (in seconds) so that data from before the start of the simulation can be shifted into the simulation period. If this is not the case, the first lag_seconds of the simulation period will contain null data. 
Constructs a time series for a specified tidal constituent, amplitude and phase lag, using linear interpolation.
Arguments 
Description 

constituent name  The unit code for a tidal constituent. 28 constituents, derived from the Admiralty Tide Tables (time zone referenced by Greenwich Mean Time (GMT)), are supported and are described in the Tidal constituents table. The unit code for the constituent must be enclosed in quotation marks, i.e. "". 
amplitude  The mean amplitude, in units, such as meters 
phase lag  The phase lag for each tidal constituent, in degrees 
timestep  The timestep to be used, in minutes. A timestep must not be less than 0.1 minutes. 
Semidiurnal constituents
InfoWorks ICM Unit Code 
Name 
Description 
Hourly Speed (°) 

M2 
M2 
Principal lunar constituent 
28.98 
S2 
S2 
Principal solar constituent 
30.00 
N2 
N2 
Allow for the changes in the Moon's distance due to its elliptic orbit round the Earth 
28.44 
L2 
L2 
29.53 

K2 
K2 
Allow for the effect of the declination of the Sun and Moon and of changes in the Sun's distance 
30.08 
T2 
T2 
29.96 
Diurnal constituents
InfoWorks ICM Unit Code 
Name 
Description 
Hourly Speed (°) 

K1 
K1 
Allow for the effect of the Moon's declination 
15.04 
O1 
O1 
13.94 


K1 
Allow for the effect of the Sun's declination 

P1 
P1 
14.96 

Q1 
Q1 
Allow for the effect of changes in the Moon's distance on K1 and O1 
13.40 
M1 
M1 
14.49 

J1 
J1 
15.59 
Quarterdiurnal constituents
InfoWorks ICM Unit Code 
Name 
Description 
Hourly Speed (°) 

M4 
M4 
First shallow water harmonic of M2 with a speed twice that of M2 
57.98 
MS4 
MS4 
Shallow water constituent produced by the interaction of M2 and S2, with speed equal to sum of speeds of M2 and S2 
58.98 
Other constituents
InfoWorks ICM Unit Code 
Name 
Description 
Hourly Speed (°) 

SA 
Sa 

0.041 
SSA 
SSa 

0.082 
MM 
Mm 

0.544 
MSF 
MSf 

1.015 
MF 
Mf 

1.098 
PI1 
p1 

14.92 
2N2 
2N2 

27.90 
MU2 
µ2 

27.97 
NU2 
u2 

28.51 
M3 
M3 

43.48 
M6 
M6 

86.95 
2MS6 
2MS6 

87.97 
2SM6 
2SM6 

88.98 
M8 
M8 

115.94 