Understanding complex SQL queries

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.

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

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

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

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

A notification appears, stating that the syntax is valid.

8. Click OK.
9. Click Save.
10. Click Run.

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.

11. 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.

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

To rerun the query:

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

Again, an SQL results grid appears.

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.