SQL example: Validation

It is possible to use SQL to create your own, more complex, validation rules. The following example is taken from the Innovyze product InfoAsset Manager but the basic principles are relevant to whichever Innovyze product you are using.

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

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

Before doing this, make sure the field you use doesn't contain useful information!

Now we count all the connections to pump objects. Note that the Object Type field for this query is set to Pump.

  • in the SQL box there are actually two SQL queries separated by a semi-colon
  • the us_node.user_number_2 syntax allows you to access data fields for the node upstream (and 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

Next find all the pumps that are connected to other pumps. These will now be selected.

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

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. Queries are executed in alphabetical order, so all you need to do is be careful when naming them.

SQL examples

SQL and stored queries