Page 9 of 10 FirstFirst ... 78910 LastLast
Results 81 to 90 of 92

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

  1. #81
    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,

  2. #82
    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];

  3. #83
    Forum Moderator

    Innovyze Employee



    Join Date
    Feb 2013
    Posts
    133
    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;

  4. #84

    Join Date
    Jun 2018
    Posts
    7
    Quote Originally Posted by Rui Daniel Pina View Post
    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;

  5. #85

    Join Date
    Jun 2018
    Posts
    7
    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;

  6. #86

    Join Date
    Dec 2014
    Posts
    9
    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.

  7. #87
    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);

  8. #88

    Join Date
    Jun 2018
    Posts
    7
    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?

  9. #89

    Join Date
    Aug 2013
    Posts
    22
    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.

  10. #90
    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<3m2/s
    Partial damage: V>2m/s AND 3m2/s<D*V<7 m2/s
    Destroyed: V>2m/s AND D*V>7 m2/s
    Is there any way to use an SQL to define a theme for these results categories?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •