SQL example: Validation

It is possible to use SQL to create your own, more complex, validation rules.

In the example network in question, it is invalid to connect a pump station object directly to another pump station object. This series of SQL queries makes use of the user defined field user_number_2 to count connections to pump stations. At the end, any pump station that is connected directly to another pump station will be selected.

Step Step Description Query Example Image

A

First we set the user field value in all nodes to zero.

Important: Before doing this, make sure the field you use doesn't contain useful information!
SQL query context:
  • Object Type: All Nodes 


SET user_number_2 = 0

B

Now we count all the connections to pump station objects by incrementing the number in the user number field for each connection. Note that the Object Type field for this query is set to Pump Station.

  • in the SQL box there are actually two SQL queries separated by a semi-colon.
  • the us_node.user_number_2 / ds_node.user_number_2 syntax allows you to access data fields for the node upstream/downstream of the current object. This is a very useful tool.
  • the two SQL queries add one to the user number fields of the connected nodes to indicate that they are connected to a pump station.
SQL query context:
  • Object Type: Pump Station 


SET us_node.user_number_2 = us_node.user_number_2 + 1;
SET ds_node.user_number_2 = ds_node.user_number_2 + 1

C

Next, find all the pump stations that are connected to other pump stations. These will now be selected.

SQL query context:
  • Object Type: Pump Station 


us_node.user_number_2 > 1 OR ds_node.user_number_2 > 1

D

Finally, delete the count from the User Number 2 fields as we do not need it any more.

SQL query context:
  • Object Type: All Nodes 


SET user_number_2 = NULL

E

The SQL Queries that make up this validation process can be placed in a Stored Query Group. You can then run all the queries in the group on your network by dragging and dropping the Stored Query Group object onto the GeoPlan. Queries are executed in alphabetical order, so all you need to do is be careful when naming them.

Parent topic: SQL examples