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.