Adding SQL Expressions to TVD Connectors

An SQL expression can be added to a TVD connector, allowing data in a TSDB to be transformed into a value which is appropriate for your model. For example, you may use an expression to apply a rating curve or fill in missing data in the data stream (overriding the usual priority order of the three input data streams). Any SQL expressions will be automatically evaluated during an appropriate Run.

Adding an expression

To add an SQL expression to a TVD connector

1. Ensure that the relevant network is opened in the GeoPlan.
2. Select the Grid WindowsNew polygon window option from the Window menu or use the button to display the polygon grid window.
3. Select the TVD connector tab to display the TVD Connector Data Fields.
4. If you are creating a new TVD connector, fill-in the relevant fields in the grid.
5. In the Output Expression field, click to display the SQL Expression dialog, which can be used to type in the expression.

   

   The TVD Connector - SQL Expression Dialog topic contains a list of the functions and operators which can be included in the expression.

   If you are using the editor, then:

   1. Click Builder >>. This displays a set of buttons representing some of the operators that you can include in the expression.
   2. Specify the expression in the pane.

   You can use the Field dropdown list to select the database fields for the TVD connector that you want to include in the expression. The functions and operators which can be included in the expression are listed in the TVD Connector - SQL Expression Dialog topic.

   3. Test the expression as described below.

   If the test is unsuccessful, then you may need to edit the expression. If it is successful:

   4. Click OK to save the expression and close the dialog.

   The saved expression is now displayed in the Output expression field.

6. Select the corresponding units from the Connector units dropdown list in the TVD connector's data fields.

Testing the expression

To test that the expression contains valid SQL functions and operators

1. Drag a TSDB object from the database tree and drop it on the InfoWorks or SWMM network (which contains the object to which this TVD connector is connected) in the GeoPlan.
2. Click Test. The Test SQL dialog is displayed, which, as default, displays the current date and time.

   

3. Use the dropdown calendar to set the start and end date and time to be used in the test. If the Field type is set to Run origin, then you will also need to set the run origin time.
4. Click OK.

If the expression is valid, a window containing the results of evaluating the expression, is displayed.

Example of a Results Window for a Valid Expression

However, if the expression is invalid, a suitable warning message will be displayed in the window.

Example of a Results Window for an Invalid Expression