Results 1 to 1 of 1

Thread: Contains Queries -> Queries that search for specific characters in a field or ID

  1. #1
    Forum Moderator

    Innovyze Employee

    Innovyze Employee

    Join Date
    May 2015

    Lightbulb Contains Queries -> Queries that search for specific characters in a field or ID

    Innovyze InfoWater, H2ONET, and H20MAP Water software has the ability to create queries that can search for words or letters that are contained in a field.

    This can be useful if your model uses specific naming conventions to quickly search for elements with certain letters in an ID for instance.

    Say your model has hydrants using ID's such as HYD_001, HYD_002, etc and you wanted to quickly find them.

    In a junction query you can do this using a "contains" type query which uses the "$" symbol.

    the query would say 'HYD'$ID or ( 'HYD' $ ID ) to search for junction ID's that have the letters 'HYD' in the ID (i.e. they contain these letters in that field)

    The letters have to be found consecutively in the field to be selected but can be found anywhere in the field.

    This can be useful for finding elements simply based on the way they are labeled if a type of smart ID is used to indicate what type of element it represents and can be used to potentially eliminate the need to use a separate field to indicate the type of element.

    Examples of how one might make use of "contains" type queries in a model:
    1) Identifying elements by their ID naming
    If your model elements follow a well thought through naming convention elements of a certain type can be found using a contain query searching for key elements in the ID
    Finding Hydrants with 'HYD" in the ID like HYD_001 -> 'HYD' $ ID
    Finding Valves with VLV in the ID like VLV_098 -> 'VLV' $ ID
    Finding pipes marked as IO for Inlet-Outlet -> 'IO' $ ID
    The sky is the limit for these queries if your naming conventions are consistent

    2) Use Field to identify junctions or pipes with certain codes for quality control in Connectivity
    If you identified all Network Review fix codes found in a field Called "QAQC_CODE" where names like as follows are listed, NICP (nodes in close proximity) or PSC (pipe Split Candidate, PP (Parallel Pipe), etc but only wanted to select elements having a specific code a contain query is very handy.
    To select all junction elements having NICP use the following -> 'NICP' $ JUNCTION->QAQC_CODE
    To select only junctions with PSC use the following -> 'PSC' $ JUNCTION->QAQC_CODE
    This gives the use the ability to place all QAQC codes into a single field, but to still quickly query out elements that have a specific code.

    3) Identifying pipes within a specific street
    If you are importing additional data from GIS and the GIS lists the street name in a field mapped to your model pipes (say to a field called 'STREET') you can search for pipes within a given street by using a contains query.
    So to look for pipes in a street 'Juan Tabo" once can query a pipe query as follows -> 'Juan Tabo' $ PIPE->STREET
    This would put into the domain all pipe names that contain the words 'Juan Tabo" in the field called 'STREET'
    4) Quickly find Booster Pumps vs. Well pumps
    If your pumps are named such they have naming differences between Well pumps and Booster pumps such as Charles_BP_01 vs Charles_W_02 then a contain query could be used to do this as follows.
    Again, this is the benefit of using advanced facility element naming within your model.
    Booster pump Query -> 'BP_' $ ID
    Well pump Query -> 'W_' $ ID
    5) Finding pipes connected to elements with certain types of ID's
    To identify any pipes connected either upstream or downstream of elements with certain characters in the ID once could do as follows and query the TO and FROM fields for certain ID letters
    To find pipes connected to Hydrant elements with HYD in the ID -> FROM='HYD' $ID .OR. TO='HYD' $ ID
    To find pipes connected to elements having CV in the to our from nodes -> FROM='CV' $ID .OR. TO='CV' $ ID
    The possibilities are pretty endless if you choose consisted ID's for your elements
    6) Finding key phrases used in the element description
    When element ID's are not always usable or "smart" as noted, the element description is often a field that can be used to search for key elements if the description field is populated. We most often see this used for valve, pump, and Tank elements which often contain the typical name that element is called. If the description is populated consistently, this field could also be used to query certain elements as long as it is always populated with the key phrases needed
    Example Find all pumps with the work Booster in the description -> 'Booster' $ PUMP->DESCRIPT
    Example find all valves marked as PRV in the description -> 'PRV' $ VALVE->DESCRIPT

    Contains type queries can be quite useful to the user once you know how to use them and are especially useful when element ID's have consistent naming conventions to quickly find elements of a certain type just from the ID's. They can be used to query any field including the element ID to search for key letters or words contained within that field. We recommend you review the use of contains queries and add them to your modeling "toolbox" as another useful tool to help you better model.

    Patrick Moore
    Last edited by Patrick Moore; October 12, 2017 at 11:10 AM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts