Page 11 of 11 FirstFirst ... 91011
Results 101 to 103 of 103

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

  1. #101
    Quote Originally Posted by Jason_Kulsdom View Post
    Is there an SQL term that gives the genuine subcatchment (polygon) area? I am trying to write an audit SQL that identifies all subcatchments where the total runoff area is greater than the total subcatchment area, but I want to use the genuine area of the subcatchment - not the value which has been entered in the "total area" field (which may not equal the actual area)? So something along the lines of:

    CLEAR SELECTION;
    Subcatchment.area < area_absolute_1 + area_absolute_2 + ... + area_absolute_12
    If you set to subcatchment total area to #D flag, then it changes to the polygon area.

    SET $area_store=total_area, $area_store_flag=total_area_flag;
    SET total_area_flag="#D";
    SET $true_area=total_area;
    SET total_area=$area_store, total_area_flag=$area_store_flag;



    CLEAR SELECTION;
    SELECT WHERE $true_area < area_absolute_1 + area_absolute_2 + ...etc

    The first line stores the current total area and total area flags into variables
    Then we change the total area flag to default, which changes the total area to the polygon area. This is then stored in a variable ($true_area).
    We then retrieve the total area and total area flag to set them back to their original values. You now have the '$true_area' which you can use in the rest of the query.
    Alternatively, you could store the polygon area in a user number field, rather than a variable if you want to be able to actually compare them.

    As to your second query; I don't believe there are any other group shortcuts. The way you've done it is how I would also do it.

  2. #102
    I'm really new to programming with SQL and still don't fully understand it, so looking for some help.

    How can I program a script to change conduit pipe sizes based on the nominal sizes? For example, within the current selection, if the pipe is 2700mm, I want it reduced to 2550mm. If it's 1200mm I want it reduced to 1050mm, and so on. Having trouble using the CASE or IF statements, or maybe it should be some sort of loop through all the selected conduits? Any help is appreciated.

    EDIT: I've been working on this and have the following code, but it only updates one of the selected conduits. (Storing the desired value in User Number 9 until I've got it all figured out)

    LIST $NomPipe = 250,300,375,450,525,675,900,1050,1200,1350,1500,16 50,1800,2100,2400,2550,2700,3000;
    LIST $ID STRING;
    SELECT SELECTED DISTINCT oid INTO $ID FROM [Conduit];
    LET $i=1;
    WHILE $i<=LEN($ID);
    DESELECT ALL;
    SELECT FROM [All Links] WHERE oid=AREF($i,$ID);
    SET $a = INDEX(conduit_width,$NomPipe);
    UPDATE SELECTED SET user_number_9 = AREF($a - 1,$NomPipe);
    LET $i=$i+1;
    WEND;
    Last edited by Dave White; June 18, 2020 at 07:56 PM.

  3. #103
    Forum Moderator

    Innovyze Employee



    Innovyze Employee



    Join Date
    May 2015
    Posts
    434
    Dave,

    For SQL help I would recommend sending an email to Support@innovyze.com. There are not as many Wastewater users who are active on the forum at this time, but this way (as long as you have an active InfoCare) you generally can get the fastest assistance for your inquiry.

    Patrick Moore

    Quote Originally Posted by Dave White View Post
    I'm really new to programming with SQL and still don't fully understand it, so looking for some help.

    How can I program a script to change conduit pipe sizes based on the nominal sizes? For example, within the current selection, if the pipe is 2700mm, I want it reduced to 2550mm. If it's 1200mm I want it reduced to 1050mm, and so on. Having trouble using the CASE or IF statements, or maybe it should be some sort of loop through all the selected conduits? Any help is appreciated.

    EDIT: I've been working on this and have the following code, but it only updates one of the selected conduits. (Storing the desired value in User Number 9 until I've got it all figured out)

    LIST $NomPipe = 250,300,375,450,525,675,900,1050,1200,1350,1500,16 50,1800,2100,2400,2550,2700,3000;
    LIST $ID STRING;
    SELECT SELECTED DISTINCT oid INTO $ID FROM [Conduit];
    LET $i=1;
    WHILE $i<=LEN($ID);
    DESELECT ALL;
    SELECT FROM [All Links] WHERE oid=AREF($i,$ID);
    SET $a = INDEX(conduit_width,$NomPipe);
    UPDATE SELECTED SET user_number_9 = AREF($a - 1,$NomPipe);
    LET $i=$i+1;
    WEND;

Posting Permissions

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