SQL Emojis - 📊 Understanding Array Fields and SQL Functions

 📊 Understanding Array Fields and SQL Functions

Array fields are dynamic data containers often presented in grids on property sheets, representing various details like CCTV survey specifics, manhole survey inlets, and outlets within InfoAsset Manager. Each row in these arrays corresponds to a specific data point within these surveys or objects, akin to the way objects themselves have named fields.

📍 Flag Arrays: Flags associated with an object can also be treated as arrays. They contain two essential fields - "value" and "name" (where "name" is read-only).

SQL provides several ways to access and work with the fields within these arrays.

🔍 1. Detecting Data in the Array: To check if the array contains any data, you can use the function ANY with the array's name followed by '.'. For example, ANY(details.) will return true if there are any records in the array, otherwise, it returns false.

📏 2. Counting Rows in the Array: Counting the number of rows in the array is done using the COUNT function with the array's name followed by '.'. For instance, COUNT(details.) calculates the number of rows in the "details" array.

📊 3. Aggregate Functions: SQL offers various aggregate functions that evaluate an expression for each row in the array and then perform a collective action with all the results. These functions can be applied to numeric, date, and string fields. Some key aggregate functions include:

• ANY: Returns true if the expression is true for any row.
• ALL: Returns true if the expression is true for all rows.
• COUNT: Returns the count of records that satisfy the expression.
• MAX: Retrieves the maximum value from the array.
• MIN: Retrieves the minimum value from the array.
• AVG: Computes the average of non-NULL values.
• FIRST and LAST: Capture values from the first and last records in the array.

📦 Using 'Bare' Array Field Values: You can also use values from array fields outside of aggregate functions in expressions. The expression is evaluated for each record within the array. Keep in mind that when using expressions, it's crucial to precisely define your query, especially when dealing with tests other than equality.

📝 Setting Values in Array Fields: You can set values in array fields by including them in an assignment clause. This allows you to modify data in multiple records at once. The SET clause can be used with or without a WHERE clause, and you can use array fields to specify the conditions under which the SET clause is applied.

🔁 Interaction Between 'Bare' Array Fields and Aggregate Functions: When using array field values in both the WHERE and SET clauses, the aggregate functions in the SET clause are only evaluated for records that meet the conditions specified in the WHERE clause.

For example, if you use the query SET user_number_1 = COUNT(details.*) WHERE details.code = 'DES', it will set user_number_1 to the number of "details" records with the code "DES." The same effect can be achieved with SET user_number_1 = COUNT(details.code='DES').

An alternate way to calculate this without using a 'bare' array field would be SET user_number_1 = SUM(IIF(details.code='DE', details.service_score, 0)).

However, if an aggregate function is used in the WHERE clause, it will be applied to all records within the array field.

🔗 In Summary: SQL's array handling capabilities provide a powerful means to manipulate and analyze complex data structures efficiently. Using aggregate functions and 'bare' array field values wisely can help you unlock the full potential of your data.