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

[Fraser.Smith]

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.

[Dave White]

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;

[Patrick Moore]

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

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;