PDA

View Full Version : Do you use SQL queries to build your model or analyse your simulation results?



Duncan Kitts
March 21, 2013, 02:39 AM
If so which ones do you use?

SQL's or Structured Query language is a highly powerful and flexible way to select objects and edit data based on the object properties. They're particualrly useful for changing values and selecting nodes/links which have particular results.

If you have got some useful ones, please post them in this thread so that others can use? Hopefully we can start an online repository so that modellers can see how SQLs are used and see some examples. That way we can all build and analyse our models more efficiently.

Iain Scott
March 25, 2013, 06:33 AM
If I have a selection list of nodes, can I select the immediate downstream links using SQL? (or any other method, really!)

Andrew Walker
March 26, 2013, 05:41 AM
Yes, you can.

Lets assume the selection is to be done on the model data (rather than a set of results).

It can be done as a single operation, but requires two SQL's, so start by creating an SQL Group (you'll see why in a moment!).

In the SQL Group, create your first SQL, which sets a User-Text value on the link attached to the selected nodes (Start with "All Nodes" as your object type). You must tick the box to apply the change to the currently selected nodes.

SET ds_links.user_number_1=99

Now create a second SQL which is based on "All links"

SELECT WHERE user_number_1=99

When saving your two SQL's, prefix the first with "1" and the second with "2" (again, you'll see why in a moment!).

To automatically apply each of the two SQL's in sequence you just need to drag the SQL Group onto the GeoPlan. When you drag a Group onto the GeoPlan all of the SQL's in the group are applied in sequence (that's why I suggested you name each of the SQL's in alphabetical or numerical order).

All links connected to the previously selected nodes are now selected.

Kristian Ravnkilde
March 27, 2013, 02:25 AM
Clever. How about manholes/ links with no upstream subcatchments? I have never managed to automate that, and they can cause major instabilities, especially in quality models.

Iain Scott
March 27, 2013, 04:44 AM
Thanks, Andrew: that works very well.

David Garcia
April 1, 2013, 02:07 PM
We rely heavily on SET queries when doing model build, clean-up, and validation. It makes the process much faster being able to make changes quickly and have the data be flagged properly at the same time.

Martin A Spiers
May 3, 2013, 07:25 AM
You can start with manhoels with no upstream link, and prune iteratively

Kristian Ravnkilde
May 7, 2013, 01:34 AM
That sounds like a manual job, or do you have a way of selecting any with no upstream connected area?

Duncan Kitts
July 18, 2013, 04:33 AM
Below is a useful SQL for exporting cross-section data (x,y,z and Mannings n) to a csv file:-

select id, section_array.X, section_array.Y, section_array.Z, section_array.roughness_N DP 4 INTO FILE 'd:\mysections.csv'

Note that the DP4 means that the Mannings n value is exported to 4 decimal places rather than the default 2. Useful for exporting and displaying the relevent decimal places.

Robert Dickinson
July 18, 2013, 04:35 AM
Below is a useful SQL for exporting cross-section data (x,y,z and Mannings n) to a csv file:-

select id, section_array.X, section_array.Y, section_array.Z, section_array.roughness_N DP 4 INTO FILE 'd:\mysections.csv'

Note that the DP4 means that the Mannings n value is exported to 4 decimal places rather than the default 2. Useful for exporting and displaying the relevent decimal places.

Thanks, that is very useful, Duncan!

Duncan Kitts
February 26, 2014, 03:18 AM
A new one that I came accross today. If you are importing data into a table, the Table Name is identical to that specified in the 'Object Type' section of the SQL editor. If the Table name includes spaces then the table name should be contained within square brackets. The space is NOT represented using an underscore.

For instance to add data to the Runoff Surface table the SQL table reference is:-

[Runoff Surface] NOT Runoff_Surface

Duncan Kitts
February 26, 2014, 03:29 AM
Note, that the actual table name is case-insensitive.

Duncan Kitts
October 3, 2014, 02:14 AM
Set the node system type to be the same as that of the connecting subcatchment:

Set system_type=subcatchments.system_type

Duncan Kitts
November 20, 2014, 03:04 AM
The following will provide a table of a count of all the U/S Headloss values grouped into integer classes:

SELECT COUNT (*) GROUPBY INT(us_headloss_coeff)

77

Duncan Kitts
July 24, 2015, 08:41 AM
A way of working out the duration of time that a pump is flowing greater than 0 for:-

Select Duration (tsr.ds_flow>0)

Note, that this will be the total duration and may not be contiguous. It is possible to limit the time periods within which the query is run. This is only available in version 6 or later.

Duncan Kitts
July 24, 2015, 08:42 AM
A way of automatically values to a storage array for a storage node.

Insert Into [node.storage_array] (node_id, storage_array.level, Storage_array.area) SELECT node_id,user_number_1,1000;
Insert Into [node.storage_array] (node_id, storage_array.level, Storage_array.area) SELECT node_id,user_number_2,1000;

In this case the user number 1 and 2 were used to take invert/soffit levels from upstream and downstream conduits.

Duncan Kitts
October 16, 2015, 02:27 AM
Count number of flooding nodes and output to a csv table together with the total flood volume


SELECT COUNT (sim.max_floodvolume>0) AS "Flooded Manholes", SUM(sim.max_floodvolume) AS "Total Volume" INTO FILE 'D:\flood volumes.csv'

Duncan Kitts
October 16, 2015, 02:37 AM
A check for upstream link width greater than downstream link width. This is applied to 'All Nodes' object type

us_links.conduit_width>ds_links.conduit_width

Duncan Kitts
January 21, 2016, 01:25 AM
198

This query will update the node ground level with the mesh level zone assigned elevation in which the node resides.

Duncan Kitts
January 21, 2016, 01:29 AM
This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments:-

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;
SELECT FROM Subcatchment WHERE $subcatchment_selected =1;

DESELECT ALL

The while keyword will select all nodes upstream for a count of 100. If you need this to be larger you can edit the number as required.

Rajan
February 19, 2016, 11:25 AM
is anyone using sql to analyze the result to get hourly peak flow, 24 hour peak flow out of link result table?

Duncan Kitts
March 30, 2016, 07:45 AM
The following will provide a list of pipe names with the pipe downstream average velocity for the simulation. It can be easily modified for upstream velocity or flow.

SELECT us_node_id, link_suffix,AVG(tsr.ds_vel)

Duncan Kitts
April 28, 2016, 07:13 AM
LET $n = 0;
PROMPT TITLE "Manning's n for River Reach Sections";
PROMPT LINE $n "Manning's n value" DP 3;
PROMPT DISPLAY;
SET sections.roughness_N=$n

The above query will provide a prompt dialogue where a user can specify a Manning's n value which is then used to update the river reach section roughness. You could use this in conjunction with the custom actions (http://blog.innovyze.com/2015/10/16/custom-actions/) functionality to speed up building river models. A similar thing could be done with Bank coefficients

Abhi
May 6, 2016, 08:16 AM
Is there SQL query to select manholes without upstream link?

DS Wessex
May 16, 2016, 02:20 AM
Duncan

Will Innovyze be publishing the SQLs/tips that have emerged? (the post is rather long to look through)

Duncan Kitts
May 16, 2016, 03:00 AM
Duncan

Will Innovyze be publishing the SQLs/tips that have emerged? (the post is rather long to look through)

No plans to. This post was intended to be a single location for queries.

Guillermo
May 23, 2016, 02:26 AM
Mr. Abhi, this should work
count(us_links.*)=0

Duncan Kitts
July 12, 2016, 02:26 AM
A query to determine which subcatchments are using a particular runoff surface:-


CLEAR SELECTION;
SET $runoffSurfaceID="111";

SELECT FROM [land use]WHERE runoff_index_1= $runoffSurfaceID or runoff_index_2= $runoffSurfaceID or runoff_index_3= $runoffSurfaceID or runoff_index_4= $runoffSurfaceID or runoff_index_5= $runoffSurfaceID or runoff_index_6= $runoffSurfaceID or runoff_index_7= $runoffSurfaceID or runoff_index_8=$runoffSurfaceID or runoff_index_9= $runoffSurfaceID or runoff_index_10= $runoffSurfaceID or runoff_index_11= $runoffSurfaceID or runoff_index_12= $runoffSurfaceID;

LIST $landUseID STRING;
SELECT SELECTED DISTINCT land_use_id INTO $landUseID;
LET $i=1;
WHILE$i<=LEN($landUseID);

SELECT FROM subcatchment WHERE land_use_id=AREF($i,$landUseID);

LET $i=$i+1;
WEND;

SCALARS


Justchange the line SET $runoffSurfaceID ="111"; with the runoff surface you area looking for.

Duncan Kitts
August 5, 2016, 02:59 AM
The following will allow create a table of subcatchments, the rainfall profile and the simulated NAPI value at a particular point in time.

SELECT subcatchment_id AS 'ID', rainfall_profile AS 'Rain gauge', MAX(tsr.napi) AS 'NAPI' WHEN tsr.timestep_start = #01/01/2016 00:00#

Rui Daniel Pina
August 5, 2016, 03:39 AM
This SQL selects upstream links of selected nodes:

LIST $nodeID STRING;
SELECT SELECTED DISTINCT node_id INTO $nodeID;

DESELECT ALL;

LET $i=1;
WHILE $i<=LEN($nodeID);
SELECT FROM [All Links] WHERE ds_node.node_id=AREF($i,$nodeID);
LET $i=$i+1;
WEND;

Rui Daniel Pina
August 5, 2016, 03:44 AM
This is an example of spatial analysis with SQL. The following code selects all links and nodes inside a polygon:

LIST $PolygonID STRING;
SELECT DISTINCT polygon_id INTO $PolygonID;

PROMPT TITLE"Dialog";
PROMPT LINE $sel 'Polygon' LIST $PolygonID;
PROMPT DISPLAY;

SELECT FROM [All Nodes] WHERE spatial.polygon_id = $sel;
SELECT FROM [All Links] WHERE spatial.polygon_id = $sel;


311

Rui Daniel Pina
August 5, 2016, 04:12 AM
SQL can also be a powerful for themes in the Geoplan. In the example below, the SQL compares the 2D water depth of two simulations and shows the difference if higher than a treshold (0.1):

IIF(sim.depth2d-sim2.depth2d > 0.1, sim.depth2d-sim2.depth2d , 0.0)

You can add this simple expression in the "GeoPlan Properties and Themes > 2D Zone Theme" as shown in the next figures.

312313

DanMor
August 29, 2016, 07:21 PM
This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments

Smart, this is often useful and with a few tweaks you can get it to do other equally frequent selections. Thanks

Duncan Kitts
September 9, 2016, 01:29 AM
Clever. How about manholes/ links with no upstream subcatchments? I have never managed to automate that, and they can cause major instabilities, especially in quality models.

The following should work for all nodes, you can refine further for manholes, it sets user text 1 to 1 for all nodes that a subcatchment drain to, then selects those nodes with no upstream links and where user text 1 is not equal to 1:-

UPDATE [Subcatchment] SET node.user_text_1=1;
count(us_links.*)=0 AND user_text_1<>1

idrisnujjoo
September 15, 2016, 01:17 AM
A section of this query is also very useful in expanding from a selection of nodes to any subcatchment(s) that are associated to that node.

326

SELECT FROM [All Nodes] WHERE $node_selected = 1;
UPDATE Subcatchment SET $subcatchment_selected =0;
UPDATE SELECTED SET subcatchments.$subcatchment_selected =1;
SELECT FROM Subcatchment WHERE $subcatchment_selected =1;

Rui Daniel Pina
September 20, 2016, 09:49 AM
This is an SQL to update Head Discharge Curves.
You can add the curve to a txt file and run the script to update the curve automatically. The SQL will prompt what curve ID to update and do all the job!

The SQL script:

LIST $QDIDS STRING;
SELECT ALL DISTINCT head_discharge_id INTO $QDIDS;

LIST $CurveQ STRING;
LOAD $CurveQ FROM FILE 'C:\Curve.dat';
LIST $CurveH STRING;
LOAD $CurveH FROM FILE 'C:\Curve.dat';

PROMPT TITLE 'H-Q Name';
PROMPT LINE $UpdateID 'HQ ID' LIST $QDIDS;
PROMPT DISPLAY;

DELETE From [Head discharge].HDP_table WHERE head_discharge_id = $UpdateID;

LET $n = 1;
WHILE $n<=LEN($CurveQ);
LET $UpdateQ = AREF($n,$CurveQ);
LET $UpdateH = AREF($n, $CurveH);
INSERT INTO [Head discharge].HDP_table (head_discharge_id,HDP_table.discharge,HDP_table.h ead) VALUES ($UpdateID,$UpdateQ,$UpdateH);
LET $n=$n+1;
WEND


An example of a txt in the 'C:\Curve.dat' file:
$CurveQ,90,80,70
$CurveH,0.0,0.3,0.7

Ann Pugh
September 20, 2016, 05:03 PM
This query goes from a selected pipe(s) a number (you enter) of pipe lengths upstream.

PROMPT TITLE 'US Trace';
PROMPT LINE $USCount 'How many pipes segments upstream to select?' DP 0;
PROMPT DISPLAY;

LET $z=1;
WHILE $z <= $USCount;
LIST $nodeID STRING;
SELECT SELECTED DISTINCT us_node_id INTO $nodeID;
LET $i=1;
WHILE $i<=LEN($nodeID);
SELECT FROM [All Links] WHERE ds_node.node_id = AREF($i,$nodeID);
LET $i=$i+1;
WEND;
LET $z=$z+1;
WEND

Duncan Kitts
October 12, 2016, 03:36 AM
This query will take a selected node, trace upstream, refine the selection to include subcatchment, deslect all objects other than the subcatchments:-

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;
SELECT FROM Subcatchment WHERE $subcatchment_selected =1;

DESELECT ALL

The while keyword will select all nodes upstream for a count of 100. If you need this to be larger you can edit the number as required.

Add the following line to sum the populations in the selected subcatchments, useful for determining populations upstream of a pump catchment.

SELECT SELECTED Sum(population) FROM Subcatchment;

Duncan Kitts
December 1, 2016, 01:15 AM
INSERT INTO [Node].hyperlinks (node_id, hyperlinks.description,hyperlinks.url) SELECT node_id, 'Sheet_name', 'D:\FileName_Texttobereplaed.pdf' from node;

SET hyperlinks.url=GSUBST('D:\thisisafilename_Texttobe replaced.pdf', 'Texttobereplaced', user_text_1)

The first part of the SQL sets up a Hyperlink with the description ‘Sheet Name’ and the File Location (D:\FileName_Texttobereplaced.pdf) for all nodes.

The second part then takes the file location string and replaces the ‘Texttobereplaced’ with the User_Text_1 value (which might be a drawing number for instance). Similar can be done for links/subcatchments etc...

Duncan Kitts
December 1, 2016, 01:19 AM
Export node hyperlinks to a csv file:-

SELECT node_id, hyperlinks.description, hyperlinks.url into File 'D:/HyperlinkSpreadsheet.csv';

Similar can be done for links/subcatchments.

The following can also be used to change the url address should the folders be renamed in Windows Explorer:-

Set hyperlinks.url=SUBST (hyperlinks.url, '\OldFileName\, '\NewFileName\');

This uses the SUBST key word.

SUBST
SUBST(string,thing to replace, thing to replace it with)
Replaces the first instance of the second parameter in the first with the third e.g. SUBST('01880132','01','ND') returns 'ND880132'

Eva Choudalaki
December 6, 2016, 07:44 AM
Hi all,

Is it possible to select links with length larger than the actual distance between the u/s and d/s node? So simply links with bends?

Thanks

DS Wessex
December 8, 2016, 01:57 AM
Is there an SQL that will detectnodes where lowest outgoing invert is above lowest incoming invert?

Eva Choudalaki
December 8, 2016, 02:06 AM
MAX (ds_links.us_invert)>MIN(us_links.ds_invert)

Apply to all nodes

DS Wessex
December 8, 2016, 04:37 AM
Great, but needs to be

MIN (ds_links.us_invert)>MIN(us_links.ds_invert)

ICM User
December 8, 2016, 05:37 AM
Hi all,

Is it possible to select links with length larger than the actual distance between the u/s and d/s node? So simply links with bends?

Thanks

You could try using this on the conduit object type.

conduit_length < ((((((([us_node].x - [ds_node].x)^2) + (([us_node].y - [ds_node].y)^2))^0.5)^2) + (((us_invert) - (ds_invert))^2))^0.5)

Duncan Kitts
February 2, 2017, 04:59 AM
This query will take a selection of links and select any subcatchment draining to them using the drain to multiple links/lateral links option:-

LIST $nodeID STRING;
LIST $suffix STRING;

SELECT SELECTED DISTINCT us_node_id INTO $nodeID FROM [All Links];
SELECT SELECTED DISTINCT link_suffix INTO $suffix FROM [All Links];

LET $i=1;
WHILE $i<=LEN($nodeID);
SELECT FROM Subcatchment WHERE lateral_links.node_id= AREF($i,$nodeID) AND lateral_links.link_suffix= AREF($i,$suffix) ;
LET $i=$i+1;
WEND;

Duncan Kitts
February 2, 2017, 05:01 AM
Similar to a couple of the above but this will take selected nodes and find subcatchments draining to those nodes.

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

LET $i=1;
WHILE $i<=LEN($nodeID);
SELECT FROM Subcatchment WHERE node_id= AREF($i,$nodeID);
LET $i=$i+1;
WEND;

Kieron
February 3, 2017, 04:22 AM
Hi everyone,

Does anyone have an SQL for identifying multiple links, i.e. where there is more than one link between two nodes?

I can see there’s something in Engineering Validation, but that isn’t a feasible approach for the large model I'm working on.

Oldhoffr
February 3, 2017, 06:29 AM
Hi everyone,

Does anyone have an SQL for identifying multiple links, i.e. where there is more than one link between two nodes?

I can see there’s something in Engineering Validation, but that isn’t a feasible approach for the large model I'm working on.


I'm dying for an option like this. At my company we used to use Sobek, and still do, but Infoworks is supposed to replace it. The number one option I am missing is the ability to select two nodes, and then have the program select the shortest route between it. This allows for quick side views of different routes. Is such an option hidden somewhere, or does someone have an SQL for this?

Rui Daniel Pina
February 6, 2017, 09:35 AM
Please check the "Tracing tools" in the "Geoplan" menu. You can use the "Intermediate paths" to select all the links in paths between two selected nodes. I also recommend you to have a look to the tools "Connectivity" and "Proximity trace".

Rui Daniel Pina
February 6, 2017, 09:38 AM
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;

Kieron
February 7, 2017, 01:47 AM
Please check the "Tracing tools" in the "Geoplan" menu. You can use the "Intermediate paths" to select all the links in paths between two selected nodes. I also recommend you to have a look to the tools "Connectivity" and "Proximity trace".


Hello Rui, and thanks for your response. However, I tried using the tracing tools as you suggested and the operation never completed. The two selected manholes were approx. 75m apart, in a model of 33,000 nodes.

372


The dialogue "Intermediate Path Tracing ..." got to 15% and never went any further. I set the process going last night roughly 12 hours ago.
373

So, we're still where I started with my initial request. Is there anything else I can try?
Thanks

EDIT: as far as I can see, the suggested approach only works if you know the two nodes where there might be a more than a single direct link between two manholes - but I'm after an approach that finds all examples within a network.

Duncan Kitts
March 29, 2017, 05:10 AM
If you want to add a SUDS Control table for all Subcatchments, the following will add a SUDS control ID of, specify a SUDS control feature of DRK and set the type to 'Bio-Retention Cell':-

Insert Into [subcatchment].suds_controls (subcatchment_id,suds_controls.id, suds_controls.suds_structure, suds_controls.control_type) SELECT subcatchment_ID, '1', 'DRK', 'Bio-Retention Cell' from subcatchment

Oldhoffr
March 29, 2017, 07:19 AM
I'm trying to select all nodes ending in "c2", using
node_id LIKE '%c2'
This is however not working. Any idea why? I've also used node_id LIKE '%c2%'.
the Like operator in general doesnt seem to be working, at least not for node_id selection

Duncan Kitts
March 29, 2017, 07:42 AM
The % Operator is the modulus. I would recommend reviewing the help page particularly 'Section 1.4: String Operators' to see what is possible with the LIKE command.

Duncan Kitts
April 24, 2017, 09:19 AM
If you open a set of results, and then open another set of results as alternate for comparison, the following query will create a table of the link id, sim1 max surcharge state, sim2 max surcharge state, differences between the 2 surcharge states.

Select oid As Link_ID,

sim.max_Surcharge AS Base_Scenario, sim2.max_Surcharge As Post_Development_Scenario, sim.max_Surcharge-sim2.max_Surcharge AS Absolute_Difference;

The table columns are presented as Link_ID, Base_Scenario, Post_Development_Scenario, Absolute_Difference. This can be done for other result parameters and network objects.

Tom.Godman
April 25, 2017, 03:00 AM
Is there a way to search for values within a range?

For example I want to find every manhole where the incoming invert level does not equal the outgoing invert level; however, the difference between the two is within 1mm. This seems to have occurred where the modeller has input values to 3dp rather than the all the decimal places calculated using standard inference tools in ICM.

Duncan Kitts
April 25, 2017, 04:34 AM
Yes, you can use the logic operator, 'AND' to do something similar to the following:-
conduit_width>100 AND conduit_width<250

to select conduit with a width between 100 and 250mm.

However, I don't think you'll need to do that in your instance. I assume you want to select manholes where the difference between the US Link DS Invert level and the DS Link US Invert Level is greater than 0.001m, or:-


ABS(us_links.ds_invert-ds_links.us_invert)>0.001

Tom.Godman
April 25, 2017, 07:37 AM
Brilliant thanks, didn't think of that

Duncan Kitts
May 11, 2017, 08:01 AM
The following will add a node for each subcatchment at the subcatchment centroid (or wherever the X,Y co-ordinates are located if not default):-

Insert Into (Node_ID, X,Y) SELECT (Subcatchment_ID), (X), (Y);

The subcatchment object type needs selecting in the SQL editor.

lee
August 23, 2017, 08:37 AM
Hi,

I'm having trouble using matches with spatial querys. I have a number of similar SQLs that update the notes field where I am querying against polygons within a certain distance. There are instances where some polygons have the same name and each is returned where I would like to only include 1 result for each unique name. I have come up with the following query using the 'matches' operator which works for everything except where the result contains brackets and it is then returning all of those results again.

SET notes = IIF(notes matches '.*Interest: ' + spatial.SSSI_NAME + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +spatial.SSSI_NAME + ';"');

I have looked into using [ ] to escape the brackets and for a set string (ie 'test(test)') I can use matches '.*test[(]test[)].*' and it works but as the brackets will be in various places I'd need to escape the entire result and I'm not sure how to do this.

I've also looked at storing the result as a variable and changing the brackets to { } using SUBST which does work but with my limited SQL knowledge I can only get it to return one result per query.

Any thoughts or suggestion on the easiest way to achieve this?

Cheers

Duncan Kitts
September 20, 2017, 03:08 AM
The following query will take a selection of conduits and for each selected conduit, create a dummy node 2m downstream of the conduit downstream node (witht he suffix _COL) and connect via an orifice link with the orifice invert level set to the conduit downstream invert .

LIST $ConduitID STRING;
SELECT SELECTED DISTINCTus_node_id INTO $ConduitID;

DESELECT ALL;

LET $i=1;
WHILE $i<=LEN($ConduitID);
SELECT FROM [Conduit] WHERE us_node.node_id=AREF($i,$conduitID);

SELECT SELECTED ds_node.node_idINTO $US;
SELECT SELECTED ds_node.node_id+'_COL'INTO $A;
SELECT SELECTED ds_node.X +1.41INTO $X;
SELECT SELECTED ds_node.y +1.41INTO $Y;

SELECT SELECTED ds_invert INTO$Invert;
INSERT INTO node (node_id, x, y)values ($A, $X, $Y);
INSERT INTO orifice (us_node_id,ds_node_id, link_suffix, invert) values ($US, $A, 2, $invert);
DESELECT ALL;

LET $i=$i+1;
WEND;

Duncan Kitts
September 20, 2017, 03:11 AM
The following will calculate the floodable area for nodes within a polygon by divided the polygon area by the number of nodes within that polygon. There's a couple of optional steps in here which provide output tables to check the output.424

SET $ID=spatial.polygon_id;
SET $Area=spatial.area;
SELECT COUNT(*), $Area,$Area/Count(*) AS Flood_Area group by $ID;

LIST $polygonIDs STRING;
SELECT DISTINCT polygon_id INTO$polygonIDs FROM [Polygon];

LET $i=1;
WHILE $i<=LEN($polygonIDs);

SELECT WHERE spatial.polygon_ID=AREF($i,$polygonIDs);

SET $Area=spatial.area;
SELECT SELECTED COUNT(*) INTO$Test;
Update SELECTED SETuser_number_1=$Area/$Test;
Update SELECTED SETuser_number_2=$Area;
Update SELECTED SETuser_number_3=$Test;

DESELECT ALL;

LET $i=$i+1;
WEND;

SET floodable_area=user_number_1 WHERE flood_type='Stored';

Kristian Ravnkilde
September 21, 2017, 01:43 AM
That's so that it ends up looking like a WASSP model? Nostalgia trip!


The following query will take a selection of conduits and for each selected conduit, create a dummy node 2m downstream of the conduit downstream node (witht he suffix _COL) and connect via an orifice link with the orifice invert level set to the conduit downstream invert .


WEND;

Guillermo
October 20, 2017, 01:27 AM
I was just random searching the forum. Suppose you just found the answer a year ago, but just in case.The answer is use trigonometry:

clear selection;Set x$ = ((us_node.x-ds_node.x)^2 + (us_node_u-ds_node
.y)^2)^1/2; x$<length


Note: the "length" field name will change depending on the specific software you are using.



Hi all,

Is it possible to select links with length larger than the actual distance between the u/s and d/s node? So simply links with bends?

Thanks

Tom L
November 9, 2017, 03:40 PM
Hi,

I can't manage to run a very basic query. Would anyone know how to select a list of asset? I tried this kind of syntax but it doesn't work:

SELECT FROM [All Nodes] WHERE node_id in ('id1','id2',...)

Thanks

Tom

Guillermo
November 10, 2017, 12:24 AM
Hi Tom,
This should work:
list x$= 1,2,3; select from [all nodes] where member(node_id,x$)
Regards,


Hi,

I can't manage to run a very basic query. Would anyone know how to select a list of asset? I tried this kind of syntax but it doesn't work:

SELECT FROM [All Nodes] WHERE node_id in ('id1','id2',...)

Thanks

Tom

lee
November 13, 2017, 03:14 AM
Hi,

I'm having trouble using matches with spatial querys. I have a number of similar SQLs that update the notes field where I am querying against polygons within a certain distance. There are instances where some polygons have the same name and each is returned where I would like to only include 1 result for each unique name. I have come up with the following query using the 'matches' operator which works for everything except where the result contains brackets and it is then returning all of those results again.

SET notes = IIF(notes matches '.*Interest: ' + spatial.SSSI_NAME + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +spatial.SSSI_NAME + ';"');

I have looked into using [ ] to escape the brackets and for a set string (ie 'test(test)') I can use matches '.*test[(]test[)].*' and it works but as the brackets will be in various places I'd need to escape the entire result and I'm not sure how to do this.

I've also looked at storing the result as a variable and changing the brackets to { } using SUBST which does work but with my limited SQL knowledge I can only get it to return one result per query.

Any thoughts or suggestion on the easiest way to achieve this?

Cheers

Sorry to bump this but I'm still looking for a solution to this if anyone has any suggestions?

Thanks
Lee

Guillermo
November 13, 2017, 04:47 AM
Hi Lee,

You could try using the subst function directly over the query as follows, I think it might work:

SET notes = IIF(notes matches '.*Interest: ' + subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + ';"');




Sorry to bump this but I'm still looking for a solution to this if anyone has any suggestions?

Thanks
Lee

Tom L
November 13, 2017, 01:12 PM
Thank you Guillermo, that works perfect!

lee
November 14, 2017, 03:24 AM
Thanks Guillermo,

Appears to work! Just need to incorporate it into all my queries now.


Hi Lee,

You could try using the subst function directly over the query as follows, I think it might work:

SET notes = IIF(notes matches '.*Interest: ' + subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + '.*',notes,notes +'&CHAR(10)&"Sites of Special Scientific Interest: ' +subst(subst(spatial.SSSI_NAME, "(","{"),")","}") + ';"');

Rui Daniel Pina
January 31, 2018, 10:55 AM
This is to add multiple lateral links to subcatchments:

1. Run this spatial SQL to find the links inside subcatchments:
446
code:
set user_text_1= spatial.subcatchment_id


2. Run this SQL to add lateral links to subcatchments, with Object Type "Subcatchment" use this code:

LIST $clist STRING;
SELECT DISTINCT oid INTO $clist FROM [All Links];


let $node_id="";
let $link_suffix = "";
let $subcatchment_id="" ;


LET $i=1;
WHILE $i <= LEN($clist);
DESELECT ALL;


select user_text_1 into $subcatchment_id FROM [All Links] where oid=AREF($i,$clist) ;
select us_node_id into $node_id FROM [All Links] where oid=AREF($i,$clist);
select link_suffix into $link_suffix FROM [All Links] where oid=AREF($i,$clist) ;


IF $subcatchment_id <> "" and $subcatchment_id <> 0.00;
INSERT INTO subcatchment.lateral_links(subcatchment_id, lateral_links.node_id, lateral_links.link_suffix) VALUES ($subcatchment_id, $node_id, $link_suffix);
ENDIF;


LET $i=$i+1;
WEND;

Rui Daniel Pina
February 28, 2018, 04:56 AM
This SQL renames cross sections in river reaches. Just create a new stored query with Object type "River reach" with the following code:

LIST $listRivers string;
SELECT DISTINCT oid INTO $listRivers;


LET $j=1;
WHILE $j<=LEN($listRivers);
SELECT WHERE oid = AREF($j,$listRivers);


LIST $listXS string;
SELECT SELECTED DISTINCT sections.key INTO $listXS;


LET $i=1;
WHILE $i<=LEN($listXS);
set sections.key = oid + ':' + $i WHERE sections.key = AREF($i,$listXS) and oid = AREF($j,$listRivers);
LET $i=$i+1;
WEND;


DESELECT ALL;


LET $j=$j+1;
WEND;

Duncan Kitts
March 2, 2018, 01:48 AM
A simple query to set nodes to outfalls where they have no downstream links. Particularly useful if you're importing from asset data which doesn't specify the outfall node type explicitly.

SET node_type='outfall' where count(ds_links.*)=0

Duncan Kitts
March 12, 2018, 08:07 AM
This will remove SUDs controls from all subcatchments. You can apply to a selection if required.

'DELETE FROM [Subcatchment].suds_controls

Rui Daniel Pina
March 19, 2018, 11:19 AM
The following SQL select nodes with higher chamber_floor level in the current scenario than in the base scenario:
466

The code is:
DESELECT ALL;
UPDATE IN BASE SCENARIO SET $x = chamber_floor;
SELECT WHERE chamber_floor>$x;

Oli Moravszky
March 21, 2018, 04:16 AM
Is there a way to use SQLs to select nodes where the combined capacity of the incoming pipes exceeds the combined capacity of the outgoing pipes by a given percentage? This is useful for finding incorrect pipe diameters

Thanks
Dave Searby, Wessex Water

Guillermo
March 21, 2018, 04:29 AM
clear selection; (sum(us_links.capacity)-sum(ds_links.capacity))/sum(ds_links.capacity)*100>xx

where xx is the desired percentage.This is the fields names for ICS, for ICM don't rememeber if the field capacity has the same name. Remember that if you are changing geometry data you should validate in order to update the capacity and that certain elements differents form pipes might not have this field (such as Weirs)

DCJ
March 21, 2018, 05:50 AM
Statistics Template is a good application for model pump runtimes too.


We use a similar query for dual pumpstation starts...


SELECT us_node_id, Duration (tsr.pmpstate>0) WHERE link_suffix = '2'


We also use a script to report when "minimum_flow < nominal_flow". This is useful when changes to the system reduce pumping capacity of the existing stations.

James_F
May 28, 2018, 08:49 AM
does anyone know how to present the maximum depth from two set of results in the 2D domain? (assuming the meshes are identical)

I was hoping this would work:

(sim.max_depth2d+sim2.max_depth2d)-min(sim.max_depth2d,sim2.max_depth2d);

the fisrt bit works but min isn't recognised

Guillermo
May 29, 2018, 06:06 AM
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
June 1, 2018, 07:27 AM
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
June 11, 2018, 08:58 AM
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
June 11, 2018, 12:26 PM
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
June 13, 2018, 12:29 PM
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
June 15, 2018, 02:40 AM
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
June 28, 2018, 11:47 AM
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
July 11, 2018, 07:15 AM
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
July 20, 2018, 08:24 AM
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
July 30, 2018, 07:46 AM
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?

SWinters
August 1, 2018, 05:25 AM
Is there a possibility to set the manhole floor level X below the lowest connected invert? That would be great!

Duncan Kitts
August 2, 2018, 01:21 AM
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?

You should be able to do this with a nested IIF statement. Essentially you would have the following ranges:-

Range 1: V<2m/s OR D*V<3m2/s
Range 2: Partial damage: V>2m/s AND 3m2/s<D*V<7 m2/s
Range 3: Destroyed: V>2m/s AND D*V>7 m2/s

IIF(Range 1, No Damage, IIF(Range 2, Partial Damage, IIF(Range 3, Destroyed, Other)))

Each range would need to be defined in the IIF statement.

Jason_Kulsdom
September 3, 2018, 07:48 AM
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

As an unrelated second query, I am also trying to write an SQL to identify all objects in the model which contain a specific flag - "OP" (optioneering). So far, I have:

CLEAR SELECTION;
SELECT FROM [All Nodes] WHERE flags.value="OP";
SELECT FROM [All Links] WHERE flags.value="OP";
SELECT FROM [Subcatchment] WHERE flags.value="OP"

For the rest of the object types, would I have to add new lines for each object type? Or are there any other group terms available, along the lines of [All Polygons], [All Lines] or even simply [All Objects]?

SWinters
September 4, 2018, 07:28 AM
Does anyone use a query to set the manhole floor elevations by means of its connecting conduit? If so, I'd be very interested. Thanks!

boiermoflea
September 6, 2018, 03:18 AM
Does anyone use a query to set the manhole floor elevations by means of its connecting conduit? If so, I'd be very interested. Thanks!

In ICM, CS or InfoNet if you set the chamber floor level flag to Default, the chamber floor level will become the lowest invert level connected to the manhole.

Jason_Kulsdom
September 6, 2018, 03:35 AM
Does anyone use a query to set the manhole floor elevations by means of its connecting conduit? If so, I'd be very interested. Thanks!


In ICM, CS or InfoNet if you set the chamber floor level flag to Default, the chamber floor level will become the lowest invert level connected to the manhole.

I know that there is a quirk with ICM, whereby if you select a few pipes and use the inference to interpolate invert levels, but at the same time you do not select the intermediate nodes, then the chamber floor level of those nodes will not be updated based on the connected pipe invert levels, despite them still being flagged #D. This issue can be avoided by making sure you include all intermediate nodes before using the inference. Or it can be resolved by changing the chamber floor flags away #D, then back to #D. Or just be validating the model, at which point all fields flagged #D will re-calculate themselves.

boiermoflea
September 6, 2018, 04:34 AM
Does anyone use a query to set the manhole floor elevations by means of its connecting conduit? If so, I'd be very interested. Thanks!

set chamber_floor =iif (MIN (us_links.ds_invert) > MIN(ds_links.us_invert),MIN (ds_links.us_invert),MIN (us_links.ds_invert))
make sure you select the Apply Filter to current selection and select the manholes you want to apply sql previously.

Alberto
November 30, 2018, 10:10 AM
Hi,
Is there an SQL query to split a long conduit by adding nodes with equal intervals?
Thank you!

SWinters
February 27, 2019, 04:53 AM
set chamber_floor =iif (MIN (us_links.ds_invert) > MIN(ds_links.us_invert),MIN (ds_links.us_invert),MIN (us_links.ds_invert))
make sure you select the Apply Filter to current selection and select the manholes you want to apply sql previously.

How would I set the flad to default based on the name of a node, in particular if the name ends with an "A"?
I came up with the following, but it wont work.. any tips?

SELECT node_id LIKE '%A';
UPDATE SELECTED SET $chamber_floor_flag = '#D';

boiermoflea
March 21, 2019, 08:19 AM
How would I set the flad to default based on the name of a node, in particular if the name ends with an "A"?
I came up with the following, but it wont work.. any tips?

SELECT node_id LIKE '%A';
UPDATE SELECTED SET $chamber_floor_flag = '#D';

try following:
node_id Matches '.*a';

SET chamber_floor_flag ='#D'

Fraser.Smith
April 12, 2019, 01:43 AM
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
June 18, 2020, 12:08 PM
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
June 19, 2020, 05:28 PM
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;