Emojis in ICM InfoWorks - 7 Control Logic 🔄 8 Variables 📦 9 Other Variables 🌐

7 Control Logic 🔄

In SQL queries, you can incorporate control logic such as:

• While loops 🔄

• If.. elseif.. else... endif blocks ⚖️

For while loops, the structure is:

markdown
WHILE expression;
    <statements>
WEND

For IF statements:

php
IF expression
    <statements>
ELSEIF expression
    <statements>
ELSE
    <statements>
ENDIF

You can even use the BREAK keyword to exit loops. For instance:

bash
IF $x>0 AND $y>0
    BREAK
ENDIF

This will break out of the loop containing the BREAK keyword.

When using a WHILE loop in SQL, a progress bar appears, allowing you to interrupt the query if needed.

8 Variables 📦 To temporarily store values for use in subsequent clauses without saving them post-query, variables come handy. They offer:

1. Faster execution as they aren't written to the database 🚀.
2. Preservation of user fields for other uses 🛠.
3. Utility in setting up GROUP BY or SELECT queries to display results in grids or export them 📊.

Variables, prefixed with a dollar sign $, are automatically linked with specific object types, ensuring compatibility.

9 Other Variables 🌐 Beyond 'normal' variables, there are scalar variables and list variables.

9.1 Scalar Variables 🔢 Scalar variables hold a single value and are defined using the LET statement. For instance:

bash
LET $flag = "XP"
LET $threshold = 123.4

You can display all scalar values with the SCALARS keyword.

9.2 List Variables 📝 List variables work with 'list variable functions'. They're defined as:

bash
LIST $widths = 100, 300, 500, 700, 900

Functions like LEN, RINDEX, LOOKUP, LOOKUPFN, MEMBER, INDEX, and AREF can be used with these lists.

9.3 Table variables 📊 These are utilized in GROUP BY clauses, especially when the results are required in an implicit join.

9.4 Save and Load scalar and list variables 💾 You can save and load variables to and from files. The filename can be a string or a scalar variable holding a string. For instance:

bash
SAVE $x,$y,$xl,$yl,$zl TO FILE 'd:\temp\filename.dat'

Remember to define all variables in advance, such as:

php
LET $x = null;
LIST $x;

This is the current method to alter list variables.

Emojis - SQL in ICM InfoWorks - 10 Array fields 📋

 10 Array fields 📋

Array fields, like CCTV survey details and Manhole Survey pipes in InfoAsset Manager, typically show up in grids on property sheets. They contain multiple rows, each representing different details like CCTV survey or manhole survey pipes.

Each row possesses various named 'fields', similar to how each object has distinct fields.

Flags for an object can also be viewed as an array, with the array containing two fields: value and name (where name is read-only 🛑).

Through SQL, you can access these array fields in different ways:

10.1 Detecting if there's data in the array 🕵️‍♂️ You can check for data in the array using the function ANY with the name of the array followed by '.' - for instance, ANY(details.). This returns true if records exist in the array, and false if not.

Details about the ANY function will be discussed later in this section.

10.2 Counting the rows in the array 🧮 Using the COUNT function with the name of the array (e.g., COUNT(details.*)), you can tally the number of rows in it.

For instance, to pinpoint CCTV survey records exceeding 10 detail records, you might use COUNT(details.*)>10.

To enumerate the number of survey flags, S1, in the network: SELECT node_id, COUNT(flags.value='S1').

The nature of the COUNT function will be elaborated on later.

10.3 Aggregate functions 🔄 Aggregate functions evaluate an expression for each row in an array field, then perform an action with all the results. Here are some aggregate functions explained:

• ANY: Returns true if the expression is true for any row of the array field. For instance, ANY(details.code='JDS') checks if any row in the 'details' array has the code 'JDS'.

• ALL: This function checks if the expression is true for every row in the array.

• COUNT: Returns the count of records in the array where the expression is true. For example, COUNT(details.code=’ST’) counts records where the 'details' code is 'ST'.

• MAX: Finds the maximum value for the expression among all the records.

• MIN: Identifies the minimum value.

• AVG: Calculates the average value.

• FIRST & LAST: These return the value for the first and last records respectively.

10.4 Using ‘bare’ array field values in expressions 💡 You can utilize values from array fields both within and outside aggregate functions. For instance, details.code='GP' selects all CCTV surveys with at least one record labeled 'GP'.

It's vital to understand your query's intent, especially with tests beyond equality.

10.5 Assigning ‘bare’ array field values 📌 You can assign values to array fields using them in assignment clauses outside aggregate functions.

For example, without a WHERE sub-clause, SET pipes_in.width = 123 assigns the width for every ‘pipe in’ record in every manhole survey to 123.

10.6 Interaction between ‘bare’ array fields and aggregate functions 🔄 When you use array field values outside aggregate functions in a WHERE sub-clause, and within aggregate functions in the SET sub-clause, the aggregate functions in the SET will be evaluated only for records true to the WHERE sub-clause.

For example, SET user_number_1 = COUNT(details.*) WHERE details.code = 'DES' assigns user_number_1 the count of 'details' records for the CCTV survey with the code 'DES'.

This section provides a comprehensive overview of how to work with array fields using SQL, ensuring you can effectively manage and query your data. 📊🔍📈

Emojis - SQL in ICM InfoWorks - 13 Order By Clauses 📑

 13 Order By Clauses 📑

The 'Order By' clause 🔄 is used to determine the order in which lines are initially displayed in grids 📊 and in which they are output to CSV files 📄 when SELECT clauses are used, with or without GROUP BY clauses, to output data.

The ORDER BY clause must come after the WHERE, GROUP BY, or HAVING clause if any. For example:

SELECT COUNT() GROUP BY material, network.name HAVING COUNT() > 10 ORDER BY Count(*) 📊

SELECT node_id,ground_level WHERE node_type = 'F' 📍 ORDER BY ground_level. If there isn't a WHERE, GROUP BY, or HAVING clause, it should come after the FROM keyword used to specify a table name:

SELECT node_id,ground_level FROM [All Nodes] 📂 ORDER BY ground_level

If there's no FROM keyword specifying a table, the ORDER BY clause should come after the INTO FILE keywords and the filename:

SELECT node_id,ground_level INTO FILE 'd:\temp\selection.csv' 🗂️ ORDER BY ground_level

You can even order the results of a query without any WHERE, GROUP BY, FROM, or INTO FILE:

SELECT nodeid,ground_level ORDER BY ground_level 📊

13.1 Sorting Ascending and Descending ⬆️⬇️ To sort in ascending or descending order, use the keywords ASC and DESC respectively. If neither keyword is specified, the sorting defaults to ascending.

SELECT node_id, ground_level ORDER BY ground_level ASC ⬆️

You can also sort based on more than one expression, for instance, to sort by ground level and then by x-coordinate in ascending order:

SELECT node_id, ground_level ORDER BY ground_level ASC, x ASC ⬆️

For real numbers, sorting uses display precision (default is 2):

SELECT node_id, ground_level,x ORDER BY ground_level DP 0 ASC, x ASC ⬆️

SELECT node_id,ground_level,x ORDER BY ground_level DP 6 ASC, x ASC ⬆️

13.2 Restricting number of results 🔍 To limit the number of results, use the keywords TOP and BOTTOM.

The number of results can be capped at a specific number of items or a percentage. You can choose the top or bottom results, using either a variable or a specific number.

The percentage is calculated based on the percentage of objects to which the query applies. For example, if you have 100 nodes but only 10 are selected when the query runs, then SELECT TOP 50 PERCENT will select 5 objects, not 50. Similarly, if there's a WHERE clause, the percentage applies to the number of objects for which the WHERE clause is true.

SELECT TOP 5 node_id, ground_level ORDER BY ground_level DESC ⬇️

SELECT BOTTOM 1.5 PERCENT node_id, ground_level ORDER BY ground_level DESC ⬇️

The WITH TIES keyword allows you to extend a selection to include all objects with the same value in the 'ordered by' field as the last selected object:

SELECT TOP 10 WITH TIES ORDER BY ground_level DESC ⬇️

WITHOUT TIES can also be used, but it's not necessary as it's the default.

You can also use the keywords TOP and BOTTOM to limit the number of objects deselected, deleted, or that have values set:

DESELECT TOP 5 ORDER BY ground_level DESC 🚫

DELETE TOP 5 ORDER BY ground_level DESC ❌

UPDATE TOP 10 SET user_number_1 = 123 ORDER BY ground_level DESC 🔄

13.3 Rank 🥇 You can use the 'rank' of an object, which is the position the object appears in the list. Rank can only be used with an ORDER BY clause and only on the right side in an assignment in a SET clause:

SET user_number_1 = rank ORDER BY ground_level DESC ⬇️

If there are tied values in the sort order, all objects with the same value in the sort fields will get the same rank:

SET user_number_1 = rank ORDER BY ground_level,chamber_roof 🔄

This means objects with the same ground level and chamber roof level will have the same rank. 🥇🥈🥉