Emojis - SQL in ICM InfoWorks - 12 Explicit Select Clauses and Group By Clauses 📊

 12 Explicit Select Clauses and Group By Clauses 📊

The purpose of an explicit select clause is to generate a grid 📈 or CSV file 📄 of values with one row in the grid or file for each object meeting given criteria.

'Group by' clauses 🧮, on the other hand, allow you to calculate totals, averages, maxima, minima etc. for groups of objects e.g. by system type, length, diameter. The objects can be grouped by one or more fields, variables, expressions or some combination of these. These can also be displayed in grids or output to CSV files 📄.

It is also possible to store the results of a 'group by' clause in a 'table variable' 📑 with the aim of then using those results in a further select clause known as an implicit join.

As described later, it is possible to perform an implicit GROUP BY in which the totals, averages etc. are calculated for all objects in the table meeting given criteria. These clauses look very similar to explicit select clauses. The means by which the two are distinguished will be described below.

12.1 Explicit Select Clauses 📝 An explicit select clause may be used to generate grids or CSV files containing one or more values for each object meeting given criteria.

The main advantage of using an explicit select clause over other ways of displaying data in the software is that you can display the results of calculations, including calculations on one-to-many links and array fields.

An explicit select clause consists of the following sub-clauses, most of which are optional.

• A SELECT sub-clause containing the expressions to be output to file or grids within the software. The SELECT sub-clause is compulsory. There can be one or more expressions separated by commas, these are termed ‘sub-expressions’ 📜
• A FROM sub-clause specifying the table from which the data should be extracted. This sub-clause is optional 📤
• An INTO sub-clause specifying the file into which the results should be output. This sub-clause is optional 📁
• A WHERE sub-clause specifying a condition which should be fulfilled before the object will be considered and included in the grid or file 🔍

If no FROM sub-clause is specified, the current table will be used 🔄.

If no INTO sub-clause is specified, the results will be displayed in a grid 📈.

If no WHERE sub-clause is specified, all the relevant objects will be considered 🌐.

The keyword DISTINCT can be used with SELECT to select a group of values without duplicates e.g.

SELECT DISTINCT system_type 🚫

will display a grid of each system type in the table.

DISTINCT can be used in conjunction with WHERE and ORDER BY, but not GROUP BY, HAVING, TOP or BOTTOM.

Where multiple fields are selected, the query returns one line for each distinct set of values, rather than the distinct values for each field.

12.2 Group By Clauses 🧮 The 'Group By' clause consists of the following sub-clauses, most of which are optional.

• A SELECT sub-clause, as described above. As with the explicit select clause, this is compulsory 📜
• A FROM sub-clause, as described above. This sub-clause is optional 📤
• An INTO sub-clause, as described above. This sub-clause is optional 📁
• A WHERE sub-clause, as described above. This sub-clause is optional 🔍
• A GROUP BY sub-clause listing the fields, expressions, variables etc. used to group the objects. There can be one or more fields, expressions etc. separated by commas, these are termed ‘sub-expressions’ 📊
• A HAVING sub-clause specifying a condition for the group (rather than the object) which should be fulfilled for the data for this group to be output 🔍

If no FROM sub-clause is specified, the current table will be used 🔄.

If no INTO sub-clause is specified, the results will be output to a grid within the software 📈.

If no WHERE sub-clause is specified, all the objects will be considered 🌐.

If no GROUP BY sub-clause is specified, all the objects in the table will be considered as one group (this is known as an ‘implicit GROUP BY’) 🌎

If no HAVING sub-clause is specified, all the groups will be output to the file or grid 📁.

It is possible to use a blob field in a GROUP BY query e.g.

SELECT SUM(COUNT(details.*)) GROUP BY details.code 📊

When the result of the GROUP BY clause is output to a file, the values for all the sub-expressions in the GROUP BY sub-clause and the SELECT sub-clause will be output to the file. The records output will be sorted in an order determined by the sub-expressions in the GROUP BY clause.

It is possible to alter the text used in the header of the CSV file or the grid for each expression within the SELECT sub-clause by using the keyword AS e.g.

SELECT COUNT(*) AS MyCount, MAX(x) AS MaxX Group BY status

Note that the aliases are not strings, they do not appear in quotation marks. If you want to have spaces in the names you should deal with them as with variables with spaces in the name and use square brackets e.g. [My Title].

The aggregate functions described above, with the exception of FIRST and LAST may be used in the sub-expressions in the SELECT sub-clause. It is also possible to use the special sub-expression COUNT(*), which will return the number of objects in each group.

12.3 Implicit Group By Clause 📊 It is possible to write a query which works like a GROUP BY clause but instead of grouping things by one or more fields or expressions, considers all the objects at once. A simple example of this would be a query that counts the number of objects in the table e.g. SELECT COUNT(*) 📝

This is different from an explicit select clause e.g. SELECT node_id 📜

The rule for determining whether a query is an implicit group by or a select acting on individual objects is as follows:

• If there are no aggregate functions e.g. SELECT node_id,x,y then the query is clearly NOT an implicit GROUP BY ❌
• If there are aggregate functions acting on the results of aggregate functions e.g. SUM(COUNT(details.*)) then it clearly IS an implicit GROUP BY ✅
• If neither of the above apply then more detailed analysis has to be performed on each aggregate function as follows 🔬

If the aggregate function contains an array field or a one-to-many link followed by an asterisk e.g. SUM(details.), ANY(us_links.), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.

If the aggregate function contains a field for an array field or a one-to-many link followed by an asterisk e.g. COUNT(details.code=’Z’), MAX(us_links.width<150), then the aggregate function must be intended to work at an object level since this would not be valid at a group level.

Emojis - SQL in ICM InfoWorks - 14 Prompts 🖊️

 14 Prompts 🖊️

To allow the user 👤 to enter parameters used in queries, the statements

PROMPT LINE 📜

PROMPT TITLE 🏷️

PROMPT DISPLAY 🖥️

are used.

The fundamental way this works is as follows:

A number of PROMPT LINE statements define the list of values to be input or displayed and their formats. There will be one line displayed in a grid 📊 for each value.

The variables must be scalars. The variable can either have been defined and set before the PROMPT LINE statement or mentioned for the first time in the prompt line statement.

Valid values used in the prompt line statement are as follows, they will be explained in detail below:

PROMPT LINE <variablename> 📋

PROMPT LINE <variablename> '<description>' 📝

PROMPT LINE <variablename> '<description>' STRING 🧵

PROMPT LINE <variablename> '<description>' DATE 📅

PROMPT LINE <variablename> '<description>' DP <numberofplaces> 🔢

PROMPT LINE <variablename> '<description>' BOOLEAN ☑️

PROMPT LINE <variablename> <variablename> 🔄

PROMPT LINE <variablename> <variablename> STRING 🧵

PROMPT LINE <variablename> <variablename> DATE 📅

PROMPT LINE <variablename> <variablename> DP <numberofplaces> 🔢

All of these but the first may optionally be followed by

LIST <listvariablename> 📜

FILE 📂

FOLDER 📁

MONTH 🗓️

RANGE <startvalue> <endvalue> 📏

In general PROMPT LINE is followed by the variable name and the description. If the description is omitted as in the first case, or if the description is the blank string '', the description will be the variable name. The description may be a scalar variable which must have been defined before the PROMPT LINE clause. This is evaluated when the dialog is displayed.

Examples of valid prompt lines:

PROMPT LINE $x 📋

This defines a number variable which will have the default of 2 decimal places in the grid and the description '$x'. This takes advantage of the special case where numeric variables can be defined by simply saying PROMPT LINE $x without having to give a description.

PROMPT LINE $s '' STRING 🧵

This defines a string variable. Note that because we want to use more than 3 parameters we have to add a description field for the variable, but we can take advantage of the special case where using the blank string '' will cause the variable name to be used as the description.

PROMPT LINE $d '' BOOLEAN ☑️

This defines a boolean variable. The value will be displayed as a check box; checked for true, unchecked for false.

PROMPT TITLE '<title>' may be used to change the title of the row displayed from the default, which is 'SQL Prompt Dialog'. The title may also be a scalar variable, which will be evaluated at the point the dialog is displayed.

If you wish the entire grid to be read-only so that the values can be looked at by the user but not altered, use the keyword READONLY following PROMPT DISPLAY i.e. say

PROMPT DISPLAY READONLY 🔒

This would typically be used to display the results of SELECT queries storing values in scalars and scalar expressions (see below).

Error messages that may occur with the prompt functionality are as follows:

PROMPT LINE clause too short - the clause is too short e.g. PROMPT LINE ❌

PROMPT LINE clause requires a variable after the LINE keyword e.g. PROMPT LINE 23 - self explanatory ❓

PROMPT LINE clause variable not a scalar - the variable has already been used in another context (currently list or array) e.g. SET $x = x; PROMPT LINE $x; ⚠️

PROMPT LINE clause description not a string - the description field in the 4th parameter is not a string e.g. PROMPT LINE $x STRING (in this case perhaps you have forgotten to add a description after the variable name). 🚫

Emojis - SQL in ICM InfoWorks - 5 Assignment 📝

 5 Assignment 📝

As well as selecting ✅, deselecting ❎, and deleting 🗑️ objects, it is possible to use SQL to set values for fields in objects in the InfoWorks ICM networks 🌐. If you wish to set values for fields for objects in the default table 📊, this may be done with the SET 🛠️ keyword.

It is possible but not necessary to have a WHERE 📍 sub-clause. As with selection, deselection, and deletion, values will either be set for all objects or only for selected objects depending on the setting of the check-box ☑️.

If you do not have a WHERE 📍 sub-clause then the assignment will be done for all objects or all selected objects, otherwise it will only be performed for the objects for which the WHERE 📍 sub-clause is true.

It is possible to perform more than one assignment at once by separating them with commas.

Examples:

SET user_number_1 = 123 🔢 SET user_number_2 = user_number_1 / width ➗

SET user_number_1 = 123 WHERE x > 10000 AND y > 12000 🔍

SET user_number_1 = x, user_number_2 = y 🔄

SET user_text_1 = 'XXX' + node_id, user_text_2 = asset_id WHERE width > 200 📄 When you have more than one assignment in a clause separated by commas, they are performed from left to right, so it is safe to assume that the value of a field in one assignment will be the value that it would be after performing the previous assignments in that clause.

It is possible to override the default table and the default selection behaviour. In both cases, this is achieved by using the UPDATE 🔄 keyword.

To override the default table, the clause should begin with UPDATE 🔄 followed by the table name followed by the keyword SET 🛠️ and the rest of the clause as above e.g.

UPDATE [All Links] SET asset_id = '' 🔗

UPDATE [All Links] SET asset_id = '', user_number_1 = 0.0 WHERE width > 200 🔗

To override the default selection behaviour, begin the clause with UPDATE ALL 🔄 or UPDATE SELECTED 🔄 as appropriate.

To override both, these two mechanisms should be combined e.g.

UPDATE SELECTED Node SET user_number_1 = 123.45 🔄

It is possible to set fields to the NULL value described above e.g.

SET ground_level = NULL ❓

Ruby for CSV file Import using ODIC in WS Pro

 # Reference to the GitHub script: https://github.com/modelcreate/infoworks-ruby-scripts/blob/master/scripts/open_data_import_centre/import_model.rb

# Import the necessary library for file utilities

require 'FileUtils'

# Get the current open network in InfoWorks

open_net = WSApplication.current_network

# Prompt the user to select a folder for importing files

shp_dir = WSApplication.folder_dialog 'Select a folder to import files', true 

# The configuration (CFG) files are kept in the same directory as this Ruby script

cfg_dir = File.dirname(WSApplication.script_file) 

# Define paths for the error file and the script file

err_file = shp_dir + '\errors.txt'

script_file = cfg_dir + '\odic_script.bas'

# Define the layers and their respective configuration and shape file paths

layers = {

    "hydrant" => { "cfg" => cfg_dir + '\hydrant.cfg', "shp" => shp_dir + '\hydrants.shp'},

    "valve" => { "cfg" => cfg_dir + '\valve.cfg', "shp" => shp_dir + '\valves.shp'},

    "polygons" => { "cfg" => cfg_dir + '\polygons.cfg',  "shp" => shp_dir + '\dma.shp' },

    "pipe" => { "cfg" => cfg_dir + '\pipe.cfg', "shp" => shp_dir + '\mains.shp' }

}

# Iterate through each layer and import the data

layers.each do |layer, config|

    puts "Importing IW layer: #{layer}"

    # Skip the import if the shape file doesn't exist

    if File.exist?(config["shp"]) == false

        puts "Shape file not found for #{layer} layer - #{config["shp"]}"

        next

    end

    # Define additional options for the import

    options = {

        "Error File" => err_file,    # Location to save error logs

        "Set Value Flag" => 'IG',   # Import Flag

        "Script File"=> script_file # Import Script (ICM .rb / WS .bas)

    }

    # Execute the import function

    open_net.odic_import_ex(

        'shp',            # Source Type: Shapefile

        config["cfg"],    # Field mapping configuration

        options,          # Additional options in Ruby Hash format

        layer,            # InfoWorks Layer to import to

        config["shp"]     # Location of the shape file

    )

    # Append the error log with the completion status of each layer

    File.write(err_file, "\n End of #{layer} import \n", File.size(err_file))

end

# Read and output the content of the error log to the console

File.readlines(err_file).each do |line|

    puts line

end

# Print a completion message

puts "Finished Import"