Functions and Operators in an InfoSewer Query
Logical expressions can be developed for use on the InfoSewerH20Map Sewer Query Builder. Functions play no role in the development of Simple Controls.
Valid InfoSewerH20Map Sewer query statements are comprised of the following items:
· Operands
Operands
Operands include:
· The name of a field in a database table (ex. PIPEHYD->COEFF)
· A constant numerical value (ex. 0.013)
· A character string in single quotation marks (ex. ‘PVC’)
Logical Operators
Logical operators include:
= Equal Operand-1 is equal to Operand-2
<> Not Equal Operand-1 is not equal to Operand-2
=> Greater Than or Equal Operand-1 is greater than or equal to Operand-2
<= Less Than or Equal Operand-1 is less than or equal to Operand-2
> Greater Than Operand-1 is greater than Operand-2
< Less Than Operand-1 is less than Operand-2
Logical Connectors
Logical connectors include:
.AND. For the condition to be evaluated as true, the logical expression on both sides of the AND must be true.
.OR. For the condition to be evaluated as true, either the logical expression on one side or the other side of the OR must be true.
.NOT. Logical NOT; returns false if the expression is true and returns true if the expression is false.
( ) Parentheses for grouping logical expressions. H2OMAP Sewer evaluates expressions in the inner-most parentheses first, then moving to the outer-most parentheses.
Functions
Functions are used to convert an operand during evaluation of a logical expression. Logical functions include:
+ Addition
- Subtraction
‘ Quote for containing character strings or values in a character field. For example ‘VITRIFIED CLAY’ or ‘10’ (assuming the number ‘10’ was stored in a character field).
SUBSTR( ) Returns a substring derived from a specified character field. For example SUBSTR(‘VITRIFIED CLAY’,3,2) returns ‘TR’. SUBSTR(‘VITRIFIED CLAY’,3)returns all characters from the third position to the end of the string; ‘TRIFIED CLAY’.
UPPER( ) Converts all lowercase characters in a string to uppercase. For example UPPER(‘Main Street’) returns ‘MAIN STREET’.
CTOD( ) Converts a character string to a date object. For example CTOD(‘2/15/98’) returns 02/15/98. CTOD(‘’) returns / / .
DTOC( ) Converts a date expression to a character string. For example DTOC(02/15/98) returns ‘2/15/98’.
DTOS( ) Converts a date expression to a character string in YYYYMMDD format. For example, DTOS(02/15/98) returns ‘19980215’.
DATE( ) Returns your computers current system date. To evaluate the current date as a string, type the following: STR(DATE( )).
DAY( ) Returns the numeric value of the day of the month in a date expression. For example DAY({2/15/98}) returns 15.
MONTH( ) Returns the numeric value of the month in a date expression. For example MONTH({2/15/98}) returns 2.
YEAR( ) Returns the year of a specified date expression as a four-digit number. For example YEAR({2/15/98}) returns 1998.
LEFT( ) Returns a specified number of characters from the beginning of a character string. For example, LEFT(‘VITRIFIED CLAY’,9) returns ‘VITRIFIED’.
VAL( ) Returns a specified character string as a numeric value. For example, VAL(‘1200 Main Street’) returns 1200.
STR( ) Returns the character string equivalent of a specified number. For example STR(-32) returns ‘-32’ and STR(100) returns ‘100’.
Control
This command will initiate the pump control dialog box where the user can apply control statements to the InfoSewer model which control the status of data elements during a hydraulic simulation. Click here to learn more about control statements.
Query Report Methodology
Like a customized report, a query report is used to selectively choose which input and output data will appear in a report. However, unlike a customized report, a query report is required to relate data from an Output Relate with output data in a report format. In other words, the only way to see output data in a report format is to create a query report that contains a desired output relate.
Methodology
Do the following to create a Query Report -
- Run a Hydraulic Simulation - Using the Run Manager, run either a Steady-State, Design or EPS simulation.
- Generate Query Statement using an Output Relate - Open the DB Query Manager under the InfoSewer Browser -> Operation tab and create a new query, querying your Output Relate. For instance an Output Relate for Manholes and a DB Query identifying Manholes with elevation < 100 ft may form a good combination. Click here for more information on Querying Output Relates.
- From the InfoSewer Control Center -> InfoSewer button -> Tools menu, select the Query Report Manager command. This launches the Query Report Dialog box.
- From the Source Query ID, select the appropriate database query.
- The Available Fields box will show all of the fields available from the output relate while the Display Fields box shows which fields will be included in the query report.
- Use the Display buttons to add and remove fields from inclusion in the output report.
- When all desired fields have been added to the Display Fields box, click OK and the Query Report will be generated.
- Review and print report as desired.
Query Sets
A Query Set is a collection of individual database queries (made from the DB Query command) stored as a unique set of data. Query sets are used to group logical expressions for InfoSewerH20Map Sewer network elements (pipes, manholes, etc) for network management and graphical presentation purposes.
How Do I...
Creating a Query Set and Assigning Colors
To create a query set, from the Operation Data tab in the Control Center, highlight the Query Set folder and click on the New icon at the top of the Control Center dialog box. At this point the user is asked to provide a name and description for the new query set. When created, the user is able to add or create DB Queries that will comprise the Query Set.
Click on the icons to learn more about their functionality. Read below the following graphic to learn about the Query Set dialog box.
- Query and Color - This box shows the current DB Queries assigned to the highlighted query set. To adjust the color associated with a DB Query, highlight the desired query and click on the color box below the Delete icon. When the color palette box appears, select a color, click OK and click the Update button to see the color change in the box.
- DB Query - Select a previously created database query from the drop down box to assign to the query set. Once the desired DB Query is chosen, click on the Add button to add it to the query set.
Create DB Query - This icon will initialize the DB Query command allowing the user to create a new database query instead of having to exit the Query Set command and create a DB Query through the Control Center.- Update/Add/Delete - When a color modification is made or a new DB Query is assigned to the query set, click on the Update button to update the query set prior to clicking on OK and exiting the query set dialog box. Use Add and Delete to add and remove queries.
Once a query set is created, it is available for use in the Facility Manager, the Domain Manager, the Map Display, and the Scenario Manager (to name a few).
The following graphic shows the how the H2OMAP Sewer display is affected by a query set:
Using Query Sets in the Map Display
By viewing the Map Display tab under the Control Center, the user is able to assign a query set to the Desired Map View. Once this option is selected, the Active Query Set drop down box will appear towards the bottom of the dialog box. Select the desired query set from the drop down list and click on the Update Map button at the top of the dialog box. Once this is done, the InfoSewerH20Map Sewer display will change to the colors defined in the query set.
If the selected colors are undesirable, go back to the query set, click on the color box and adjust the color for the database queries and click again on the Update Map button on the Map Display. Repeat as necessary.