Page 9 of 9 FirstFirst ... 789
Results 81 to 88 of 88

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
    130
    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?

Posting Permissions

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