Emojis - SQL in ICM InfoWorks - 📋1 Basic Object Selection 📋

 1 Basic Object Selection 📋

1.1 Introduction to basic arithmetic and comparison 🔢 SQL can be used in its simplest form to select objects for which an expression holds true. This section focuses on basic arithmetic and string comparisons. Arithmetic operators available are:

• ➕ : Addition

• ➖ : Subtraction

• ✖️ : Multiplication / ➗ : Division ^ 📈 : Exponent (e.g. width^2, area^0.5) % 🌀 : Modulus

A 'unary minus' ➖ is used to negate a number. These operators follow the standard precedence rules.

Strings can also be joined using the '+' operator. For instance, combining 'X12' with 'Y34' yields 'X12Y34'. However, other operators don't hold special meaning for strings.

Comparison operators include:

= ✅ : Equality

= ↗️ : Greater than or equal to ⬆️ : Strictly greater than <= ↘️ : Less than or equal to < ⬇️ : Strictly less than <> ❌ : Not equal to

Comparison operators have a higher precedence than arithmetic ones.

In InfoWorks ICM, string comparison is case insensitive. When using comparison operators, behavior depends on the language set in Windows. Strings are always trimmed of leading or trailing spaces or 'tab' characters.

Fields and constants are the primary elements manipulated using arithmetic operators. Fields are categorized into:

Current table fields (e.g., node_id, x, y) Results fields (e.g., sim) Joined table fields (either one-to-one or one-to-many links) Structure blob fields

Constants are of five types: Numbers, Strings, Boolean, Dates, and NULL.

1.2 NULL field values and associated operators ❓ In InfoWorks ICM, numerical fields can be blank, represented as 'NULL' in SQL. Fields can be checked for NULL values using 'IS NULL' or 'IS NOT NULL'.

1.3 Logical operators 🔄 Expressions can be combined using logical operators AND, OR, and NOT.

1.4 String operators 🧵 LIKE and MATCHES are operators used for string tests. LIKE uses special characters '?' and '*' for matching, whereas MATCHES uses regular expressions.

1.5 Data types and type conversion rules 🔄 1.5.1 Data Types 📊 InfoWorks ICM databases contain several data types including numerical types, string, Boolean, and date.

1.5.2 Mixing Data Types in Binary Operators 🔄 Binary operators like +, -, *, and AND have rules when mixing data types.

1.5.3 Conversion to strings 🔄 Conversions include turning Boolean values to '1' (true) or '0' (false), numbers to strings, and dates to strings.

1.5.4 Conversion to Boolean 🔄 Strings with characters, dates, and non-zero numbers are considered true.

1.5.5 Conversion to numbers 🔢 Dates, strings, and Boolean values have specific rules for conversion to numbers.

1.5.6 Unary operators ➖ Unary operators include unary minus and NOT.

1.5.7 Determining which objects to select based on SQL expressions ✅ Objects are selected based on the evaluation of the SQL expression.

1.6 Simulation Results 📊 InfoWorks ICM allows running SQL queries incorporating simulation results.

1.7 One to one links 🔗 It's possible to include values for linked objects in SQL expressions. In InfoWorks ICM, this is usually done automatically through "implicit joins".

The table showcases the implicit one-to-one joins within the InfoWorks ICM Network:

From 📌 To 📍 Name 🏷️ CCTV Conduit 📺 Conduit 🚿 joined Link 🔗 Node 📌 us_node Link 🔗 Node 📌 ds_node Subcatchment 🌧️ Node 📌 node Subcatchment 🌧️ Lateral link ➡️ drains_to

This formatted structure provides a clearer understanding and easier navigation through the content. 📘