Do you use SQL queries to build your model or analyse your simulation results?

A new one that I came accross today. If you are importing data into a table, the Table Name is identical to that specified in the 'Object Type' section of the SQL editor. If the Table name includes spaces then the table name should be contained within square brackets. The space is NOT represented using an underscore.

For instance to add data to the Runoff Surface table the SQL table reference is:-

[Runoff Surface] NOT Runoff_Surface

Note, that the actual table name is case-insensitive.

Set the node system type to be the same as that of the connecting subcatchment:

Set system_type=subcatchments.system_type

The following will provide a table of a count of all the U/S Headloss values grouped into integer classes:

SELECT COUNT (*) GROUPBY INT(us_headloss_coeff)

Attachment 77

A way of working out the duration of time that a pump is flowing greater than 0 for:-

Select Duration (tsr.ds_flow>0)

Note, that this will be the total duration and may not be contiguous. It is possible to limit the time periods within which the query is run. This is only available in version 6 or later.

A way of automatically values to a storage array for a storage node.

Insert Into [node.storage_array] (node_id, storage_array.level, Storage_array.area) SELECT node_id,user_number_1,1000;
Insert Into [node.storage_array] (node_id, storage_array.level, Storage_array.area) SELECT node_id,user_number_2,1000;

In this case the user number 1 and 2 were used to take invert/soffit levels from upstream and downstream conduits.

Count number of flooding nodes and output to a csv table together with the total flood volume


SELECT COUNT (sim.max_floodvolume>0) AS "Flooded Manholes", SUM(sim.max_floodvolume) AS "Total Volume" INTO FILE 'D:\flood volumes.csv'

A check for upstream link width greater than downstream link width. This is applied to 'All Nodes' object type

us_links.conduit_width>ds_links.conduit_width

Attachment 198

This query will update the node ground level with the mesh level zone assigned elevation in which the node resides.

This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments:-

UPDATE [ALL Links] SET $link_selected = 0;
UPDATE [ALL Nodes] SET $node_selected = 0;
UPDATE SELECTED SET $node_selected = 1;

LET $count = 0;
WHILE $count < 100;

SET us_links.$link_selected = 1 WHERE $node_selected = 1;
UPDATE [ALL Links] SET us_node.$node_selected = 1 WHERE $link_selected = 1;
LET $count = $count + 1;
WEND;

SELECT FROM [All Nodes] WHERE $node_selected = 1;

UPDATE Subcatchment SET $subcatchment_selected =0;
UPDATE SELECTED SET subcatchments.$subcatchment_selected =1;
SELECT FROM Subcatchment WHERE $subcatchment_selected =1;

DESELECT ALL

The while keyword will select all nodes upstream for a count of 100. If you need this to be larger you can edit the number as required.