• InfoWorks WS Pro

Understanding complex SQL queries

Build a complex SQL query.


Tutorial resources

These downloadable resources will be used to complete this tutorial:

iwsp2023M20U05_Cmplx_SQL.zip

Step-by-step guide

In its simplest form, SQL can be used to select objects for which an expression is true. In this exercise, you write a more complex SQL query to determine the count of the total number of minutes that the customer points experience pressure below 5 meters.

  1. From the Model Group, double click SQL Control to open the Baseline simulation results.

In the InfoWorks WS Pro Model Group, Run Group and Baseline are expanded and SQL Control is called out and being selected.

Note: If the run icon on the left of the simulation is greyed out, right-click the run and select Re-run.

  1. Right-click the Stored Query Group and select New > Stored Query.

In the Model Group, Stored Query shortcut menu, New is selected, and in the flyout, Stored Query is selected and called out.

In the popup, name the stored query “Customer Minutes Lost”.

  1. Click OK.
  2. From the Model Group, double-click Customer Minutes Lost to open the SQL dialog box.
  3. Expand the Object Type drop-down and select Customer Point.
  4. In the text box, copy and paste the query provided in the Customer minutes.txt file for this tutorial.
  5. Click Test.

A notification appears, stating that the syntax is valid.

  1. Click OK.
  2. Click Save.
  3. Click Run.

In the SQL: Customer Minutes Lost dialog box, the SQL query is entered in the text box and Run is selected.

The SQL window closes, and an SQL results grid appears. In this case, there are zero minutes when customers experience less than 5 meters of pressure.

An SQL results grid for the Baseline simulation showing that there are zero minutes when customers experience less than 5 meters of pressure.

  1. Close the Baseline simulation.

Now, you can repeat this query with the Pipe Break scenario. Pipe 103874 has been set to rupture during the simulation.

  1. In the Model Group, double-click [Pipe Break] SQL Control to open it.

To rerun the query:

  1. Double-click the Customer Minutes Lost stored query.
  2. In the SQL dialog box, click Run.

Again, an SQL results grid appears.

An SQL results grid for the Pipe Break simulation showing that once the pipe ruptures, there is significantly more time that customers experience less than 5 meters of pressure.

Notice the difference in the result between the Baseline run and the Pipe Break run. Once the pipe has ruptured, there is significantly more time that customers experience less than 5 meters of pressure.

Be aware that you can find additional SQL syntax explanations and complex statements, as well as a list of SQL functions, in the WS Pro help pages on the Autodesk website.