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

[Guillermo]

Hi James,

You could try the following, it might work:

iif(sim.max_depth2d>sim2.max_depth2d, sim2.max_depth2d, sim.max_depth2d)

good luck,

[Rui Daniel Pina]

The following SQL counts the number of links that are US of a node and add it to the User Number 2:

LIST $nodeID STRING;
SELECT SELECTED DISTINCT node_id INTO $nodeID FROM [All Nodes];

LET $i=1;
WHILE $i<=LEN($nodeID);
DESELECT ALL FROM [All Nodes];
SELECT FROM [All Nodes] WHERE node_id= AREF($i,$nodeID);

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 Links] WHERE $link_selected = 1;

SELECT SELECTED COUNT(*) INTO $summ FROM [ALL Links] ;

SELECT FROM [All Nodes] WHERE node_id= AREF($i,$nodeID);
UPDATE SELECTED SET user_number_2=$summ;

DESELECT ALL FROM [ALL Links];

LET $i=$i+1;
WEND;

DESELECT ALL FROM [All Nodes];

[Duncan Kitts]

You can get the total volume in the 1d system with the following SQL query:-

SELECT SUM(sim.volume) INTO $link_volume FROM [All Links];
SELECT SUM(sim.volume) INTO $node_volume FROM Node;
LET $total_volume = $link_volume + $node_volume;
SCALARS $total_volume;

[Zesima29]

This SQL sums all the population in US sucatchments of selected nodes and store it in the user_number_1. Set the object type as "All Nodes" and use the following code:

LIST $nodeID STRING;
SELECT SELECTED DISTINCT node_id INTO $nodeID FROM [All Nodes];


LET $i=1;
WHILE $i<=LEN($nodeID);
DESELECT ALL FROM [All Nodes];
SELECT FROM [All Nodes] WHERE node_id= AREF($i,$nodeID);

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;


DESELECT ALL From Subcatchment;


SELECT FROM Subcatchment WHERE $subcatchment_selected =1;
SELECT SELECTED Sum(population) INTO $summ FROM Subcatchment;

SELECT FROM [All Nodes] WHERE node_id= AREF($i,$nodeID);
UPDATE SELECTED SET user_number_1=$summ;


LET $i=$i+1;
WEND;


DESELECT ALL FROM [All Nodes];
DESELECT ALL From Subcatchment;

How can I save it in the user number 1 from the subcatchments

I modified the last couple line to this, but it still saves on the nodes' user number 1 column

SELECT SELECTED (population) INTO $Population_1 FROM Subcatchment;
　
SELECT FROM Subcatchment WHERE subcatchment_id= AREF($i,$nodeID);;
UPDATE SELECTED SET user_number_1=$Population_1;

[Zesima29]

I did it I was able to refer to the subcatchments user columns by writing this.
UPDATE Subcatchment SET user_number_1 =
node.user_number_1, user_number_2 = node.user_number_2 WHERE subcatchment_id = node.node_id;

[A.Whitty]

Hi all,
Another SQL query...query!
I have a large amount of manhole references in which I would like to select all of the upstream links to view within the explorer table. Is this possible to do in one go? There are approximately 1,000 manholes for which I would like to select the upstream lengths.

Just to spice it up a bit, if possible...can you make the SQL only select links upstream of a certain system type?

Thanks in advance.
Adam.

[Dave Garcia]

Can anyone help me refine my query to work right? I am trying to select a node, then select upstream pipes and their associated US node in which the downstream invert is above the ground level of the selected node.


SET $elev = ground_level;


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 < 200;


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 Links] WHERE ($link_selected = 1 AND ds_invert>$elev);
SELECT FROM [ALL Nodes] WHERE ($node_selected = 1 AND ds_links.ds_invert>$elev);

[Zesima29]

I'm trying to find support for the OVER Clause in ICM SQL V8.5 but I didn't see anything related on the manual. Can someone please give me any tips on how to calculate running total?

[DCJ]

Is there a way to select the head discharge tables from a selection set of pumps? For example, I would like to be able to select, say, 10 pumps and have SQL return the notes field from the Head-Discharge Table for the selected pumps.

[Lydia FRC]

I’m working on a project looking at the risk to buildings from flooding and I'm wanting to display 2D results based on categories of risk of damage to buildings which are functions of depth and velocity. The result categories I’m wanting to show are:
No damage: V<2m/s OR D*V<3m²/s
Partial damage: V>2m/s AND 3m²/s<D*V<7 m²/s
Destroyed: V>2m/s AND D*V>7 m²/s
Is there any way to use an SQL to define a theme for these results categories?