Saturday, October 21, 2023

Emojis in SQL in ICM InfoWorks - 6 Functions 🧮

6 Functions 🧮

SQL expressions can utilize a myriad of functions alongside field values and constants. Each function needs a set number of 'arguments' and yields one value.

6.1 Aggregate functions 📊

Refer to Section 10.3 for details.

6.2 Date functions 📆

  • YEARPART 📅
    YEARPART(date) - Retrieves the year of the date.

  • MONTHPART 📆
    MONTHPART(date) - Determines the month of the date.

  • DAYPART 🌞
    DAYPART(date) - Extracts the day from the date.

  • DATEPART 🗓
    DATEPART(date) - Provides the date while removing minutes.

  • TIMEPART
    TIMEPART(date) - Gives the time in minutes from the date.

  • YEARMONTHPART 📅📆
    YEARMONTHPART(date) - Shows year and month as a string.

  • MONTHYEARPART 📆📅
    MONTHYEARPART(date) - Displays month and year in a string format.

  • MONTHNAME 📆
    MONTHNAME(date) - Returns the full month name.

  • SHORTMONTHNAME 🗓
    SHORTMONTHNAME(date) - Gives the abbreviated month name.

  • DAYNAME 🌞
    DAYNAME(date) - Fetches the full day name.

  • SHORTDAYNAME 🌤
    SHORTDAYNAME(date) - Offers the abbreviated day name.

  • NOW 🕰
    NOW() - Provides the current date and time.

  • YEARSDIFF 📅⏳
    YEARSDIFF(from,to) - Calculates the year difference between two dates.

  • MONTHSDIFF 📆⏳
    MONTHSDIFF(from,to) - Determines the month difference between two dates.

  • DAYSDIFF 🌞⏳
    DAYSDIFF(from,to) - Measures the day difference between two dates.

... and many more!

6.3 List variable functions 📝

Refer to the List Variables section for more details.

6.4 Trigonometric and logarithmic functions 📐📈

Covers functions related to angles and logs. Includes functions like ACOS, ASIN, ATAN, LOG, and more.

6.5 String manipulation 🎻

Delves into manipulating strings with functions like LEN, LEFT, RIGHT, MID, SUBST, etc.

6.6 Number conversion and formatting 🔢

Functions like FIXED help in formatting numbers.

6.7 Numerical functions 🔢🔍

Includes functions such as ABS, INT, FLOOR, CEIL and more for number operations.

6.7 Conditional expression 🔄

IIF and NVL functions that help in setting conditions in SQL expressions.

Emojis SQL in ICM InfoWorks - 11 One-To-Many Links 🌐

 11 One-To-Many Links 🌐

One-to-many links enable queries to consider linked objects, even when there's a possibility of having none, one, or multiple linked objects.

The keywords all_us_links and all_ds_links help in joining ALL upstream or downstream links to any node or link type.

Available One-To-Many Links:

FromToName
NodeLinkus_links
NodeLinkds_links
NodeSubcatchmentsubcatchments
LinkLinkus_links
LinkLinkds_links
SubcatchmentLateral linkdrains_from
SubcatchmentLateral linklateral_connected_links

🔍 How to Use them in Queries:

11.1 Determining Linked Objects 🔗

Utilize ANY(linkname.*) to check if linked objects exist. Example: ANY(us_links.*)

11.2 Counting Linked Objects 🧮

Employ COUNT(linkname.*) to tally the linked objects. For instance: COUNT(manhole_surveys.*)=0

11.3 Aggregate Functions 📊

Almost all aggregate functions from the array fields section, except FIRST and LAST, apply for one-to-many links.

11.4 'Bare' One-To-Many Fields Usage 📋

You can assign values to one-to-many fields through an assignment clause outside an aggregate function. For example:

  • With only a SET sub-clause: SET us_pipes.width = 123 sets the width for every upstream pipe.
  • With a WHERE sub-clause: SET us_links.width = 234 WHERE shaft_depth = 1650 will adjust the width for every upstream link where the shaft depth equals 1650.

11.5 Interplay Between 'Bare' Fields & Aggregate Functions ⚙️

If one-to-many field values are used outside aggregate functions in a WHERE sub-clause and inside aggregate functions in the SET sub-clause, the aggregate functions in the SET sub-clause will only be evaluated for objects linked to the current object where the WHERE sub-clause holds true.

However, if you incorporate an aggregate function IN the WHERE sub-clause, it will run for all objects linked via the one-to-many link to the current object.

AI Rivers of Wisdom about ICM SWMM

Here's the text "Rivers of Wisdom" formatted with one sentence per line: [Verse 1] 🌊 Beneath the ancient oak, where shadows p...