Understanding complex SQL queries

00:03

In its simplest form, SQL may be used to select objects for which an expression is true.

00:10

Making selections using SQL in InfoWorks WS Pro is easy.

00:16

By default, whenever an SQL Query is run,

00:20

any network objects that meet all the criteria defined in the query are automatically selected when the query finishes.

00:28

In this exercise, you will write a more complex SQL query

00:33

to determine the count of the total number of minutes the customer points experience pressure below 5 meters.

00:40

To begin, in the Model Group, double-click SQL Control to open the Baseline simulation results for this tutorial.

00:49

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

00:58

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

01:04

In the popup, name the stored query “Customer Minutes Lost”, and then click OK.

01:12

From the Model Group, double-click the Customer Minutes Lost stored query object you just created to open the Stored Query window.

01:21

Expand the Object Type drop-down and select Customer Point.

01:26

In the query window, copy and paste the query provided for you in the Customer minutes.txt file.

01:34

This query initially sets the Variables, which in this case are the time constraints and the pressure threshold of 5 meters.

01:43

The second section of the query—the Clause—determines the duration that the pressure is below 5 meters

01:51

for each customer point within the specified time constraint.

01:55

It also sums the time to provide a total time that customers will experience less than 5 meters of pressure.

02:02

With the expression complete, click Test.

02:06

The criteria dialog opens, stating that the syntax is valid.

02:11

Click OK.

02:13

Click Save to save the SQL before you run it.

02:18

Saving the SQL allows you to use it again for this model.

02:22

Now click Run.

02:26

The SQL window closes, and a window appears with the result of the SQL.

02:32

In this case, it calculated that there are zero minutes when customers experience less than 5 meters of pressure.

02:40

You can repeat this query with the “Pipe Break” scenario simulation that is also provided in the tutorial dataset.

02:48

In this scenario, Pipe 103874 has been set to rupture during the simulation.

02:56

Double-click [Pipe Break] SQL Control and rerun the query.

03:01

Analyze the difference in result between the baseline run and the pipe break run.

03:07

Once the pipe has ruptured, there should be significantly more time that customers experience less than 5 meters of pressure.

03:16

Be aware that you can find additional SQL syntax explanations and complex statements,

03:23

as well as a list of SQL functions in the WS Pro help pages on the Autodesk website.

Video transcript

00:03

In its simplest form, SQL may be used to select objects for which an expression is true.

00:10

Making selections using SQL in InfoWorks WS Pro is easy.

00:16

By default, whenever an SQL Query is run,

00:20

any network objects that meet all the criteria defined in the query are automatically selected when the query finishes.

00:28

In this exercise, you will write a more complex SQL query

00:33

to determine the count of the total number of minutes the customer points experience pressure below 5 meters.

00:40

To begin, in the Model Group, double-click SQL Control to open the Baseline simulation results for this tutorial.

00:49

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

00:58

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

01:04

In the popup, name the stored query “Customer Minutes Lost”, and then click OK.

01:12

From the Model Group, double-click the Customer Minutes Lost stored query object you just created to open the Stored Query window.

01:21

Expand the Object Type drop-down and select Customer Point.

01:26

In the query window, copy and paste the query provided for you in the Customer minutes.txt file.

01:34

This query initially sets the Variables, which in this case are the time constraints and the pressure threshold of 5 meters.

01:43

The second section of the query—the Clause—determines the duration that the pressure is below 5 meters

01:51

for each customer point within the specified time constraint.

01:55

It also sums the time to provide a total time that customers will experience less than 5 meters of pressure.

02:02

With the expression complete, click Test.

02:06

The criteria dialog opens, stating that the syntax is valid.

02:11

Click OK.

02:13

Click Save to save the SQL before you run it.

02:18

Saving the SQL allows you to use it again for this model.

02:22

Now click Run.

02:26

The SQL window closes, and a window appears with the result of the SQL.

02:32

In this case, it calculated that there are zero minutes when customers experience less than 5 meters of pressure.

02:40

You can repeat this query with the “Pipe Break” scenario simulation that is also provided in the tutorial dataset.

02:48

In this scenario, Pipe 103874 has been set to rupture during the simulation.

02:56

Double-click [Pipe Break] SQL Control and rerun the query.

03:01

Analyze the difference in result between the baseline run and the pipe break run.

03:07

Once the pipe has ruptured, there should be significantly more time that customers experience less than 5 meters of pressure.

03:16

Be aware that you can find additional SQL syntax explanations and complex statements,

03:23

as well as a list of SQL functions in the WS Pro help pages on the Autodesk website.

Video quiz

Which of the following is considered a variable when building SQL queries in WS Pro?

(Select one)
Select an answer

1/1 questions left unanswered

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.

Was this information helpful?