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. 📊🔍📈