10 Array fields Array fields are fields such as CCTV survey details, Manhole Survey pipes in and pipes out in InfoAsset Manager They typically appear in grids on property sheets because they contain a number of rows – corresponding to each detail of the CCTV survey, each pipe in and out of the manhole survey in the examples listed above. Each row has a number of named 'fields' in the same way that each object has a number of named fields. Flags for an object can also treated as an array, where the array contains two fields - value and name, (where name is read only). SQL gives you access to the fields of the arrays in a number of ways. 10.1 Detecting if there is any data in the array You can find if there are any data in the array by using the function ANY with the parameter consisting of the name of the array followed by '.*' e.g. ANY(details.*). This will return true if there are any records in the array, false otherwise. The precise nature of the function ANY will be described in a later part of this section. 10.2 Counting the number of rows in the array You can count the number of rows in the array by using the function COUNT with the parameter consisting of the name of the array followed by '.*) e.g. COUNT(details.*). For example, to select any CCTV survey records with more than 10 detail records you can say COUNT(details.*)>10. To list the number of survey flags, S1, on nodes in the network: SELECT node_id, COUNT(flags.value='S1') The precise nature of the function COUNT will be described in a later part of this section. 10.3 Aggregate functions There are a number of functions which, when applied to array fields, evaluate an expression for each row in the array field and then perform an action with all the results. The results of these functions can then be used in the same way as any other function when the expression is evaluated for the object with the array field. What an aggregate function is will become clearer when they are described below: ANY The aggregate function ANY returns true if the expression is true for any row of the array field e.g. ANY(details.code='JDS') will return true if any of the rows of the details array field has the code JDS, false otherwise. It is important to release that the expression within the brackets can contain more than one array field, other fields of the object, constants and non-aggregate functions, and these can all be combined in the same ways as before i.e. with arithmetic, comparison and logical operators, so it is possible to say things like ANY(details.code='ST' AND details.distance>0), which would detect objects where the details code is ST and the distance is greater than 0, which we might consider an error as ST is the code for a 'start' detail record and therefore the associated distance should be 0. As you can see from this example, the expression within the brackets is evaluated for each record independently so this means 'are there any details records in which the details code is 'ST' and the distance is greater than 0' not 'are there any detail records in which the details code is 'ST' and also detail records where the distance is greater than 0. If this were what was desired this could be achieved by saying ANY(details.code='ST') AND ANY(details.distance>0). ALL The aggregate function ALL returns true if the expression is true for all rows of the array field e.g. ALL(details.distance>=0) will return true if all the rows of the details array field have the distance greater than or equal to zero, false otherwise. COUNT The aggregate function COUNT returns the number of records in the array field for which the expression is true e.g. COUNT(details.code=’ST’) returns the number of records in the array field for which the details code is ST. MAX This returns the maximum value for the expression for any of the records in the array field. Only non-NULL values will be considered. MAX and MIN both work on numerical, date and string fields. In the case of string fields the comparison between strings is performed based on the language in which your Windows installation is set up. MIN This returns the minimum value for the expression for any of the records in the array field. AVG This returns the average of the value for the expression for all of the records in the array field. The average is calculated by dividing the total of the sum of all non-NULL values by the number of non-NULL values. If there are no non-NULL values AVG will return NULL. FIRST and LAST FIRST and LAST are included here with the aggregate functions but work somewhat differently, they return the value for the expression calculated for the first and last records of the array field respectively. Thus, in calculating the value, one of the records in the array field is considered. Each aggregate function can contain fields from only one array field, e.g. you can't combine fields from pipes_in and pipes_out within a manhole survey. It is, however, possible to include multiple aggregate functions within an expression, with each aggregate function containing an expression referring to a different aggregate function. e.g. COUNT(pipes_in.*)>0 AND COUNT(pipes_out.*)>0 will select all manhole surveys with both pipes and and pipes out. There is another use of aggregate functions which will be described later in this paper. 10.4 Use of ‘bare’ array field values in expressions As well as using the values in array fields within the aggregate functions described above, it is possible to use the array fields outside these functions. When they are used in expressions the expression is evaluated for each record of the array field contained in it. It is only possible to include one array field in any expression although it is possible to include multiple references to the same array field in one. details.code='GP' will select all CCTV surveys with at least one detail record with the code GP. It is important to understand exactly what you are asking for in a query, particularly if the query includes tests other than equality e.g. saying details.code<>'GP' will be true if any of the records in the array field have a code other than GP. This will almost certainly not be what you want, and certainly does not mean 'any object where none of the records of the details array field has the value GP'.
🔍 10.4 Mastering 'Bare' Array Field Queries
Dive into the world of SQL with a detective's eye 👀. When you're not using aggregate functions, 'bare' array fields are like clues scattered across a database. They're standalone pieces of information that, when used in expressions, are evaluated record by record, like following a trail of breadcrumbs 🍞.
Think of each array field as a solo artist in a gallery. You can spotlight one artist at a time in your expressions, although you can reference their multiple artworks in a single query.
For example, details.code='GP' is like sending out an invitation 💌 to all CCTV surveys that feature at least one detail with the 'GP' signature.
But beware! Queries can be tricky, like a puzzle 🧩. If you say details.code<>'GP', you're not excluding all the 'GP' artworks; you're just looking for any that are different. It's a subtle distinction that can change the whole picture of your search results.
🔑 It's crucial to be precise with your SQL queries. Ensure you know what you're asking: Are you looking for a gallery with a variety of styles, or do you want one that doesn't display a specific artist at all? The devil is in the details!
10.5 Use of ‘bare’ array field values in assignment clauses It is possible to set values in array fields by using them in an assignment clause outside an aggregate function. If there is only a SET sub-clause with no WHERE sub-clause, the SET sub-clause will be run for every record in the array field for every object in the table e.g. SET pipes_in.width = 123 will set the width for every ‘pipe in’ record in every manhole survey to 123. If there is a WHERE sub-clause that does not include a reference to the array field, the SET sub-clause will be run for every record in the array field for every object for which the WHERE sub-clause is true e.g. SET pipes_in.width = 234 WHERE shaft_depth = 1650 will set the width for every 'pipe in' record to 234 in every manhole survey where the shaft depth is 1650. If there is a WHERE sub-clause that includes a reference to the array field, then the SET sub-clause will be run for every record in the array field for which the WHERE sub-clause is true.
💼 10.5 Assigning Roles to 'Bare' Array Fields
You're the director 🎬 in this SQL theater, and the 'bare' array fields are your talented actors 🎭. They're ready to take on new roles, and it's your job to assign them with an assignment clause.
🔵 SET Sub-Clause Alone: If you send only the SET sub-clause onto the stage with no WHERE sub-clause, it's showtime for every actor in the array field! For example, if you command SET pipes_in.width = 123, every 'pipe in' character in every manhole survey will eagerly adopt the role of having a width of 123.
🔶 SET with a WHERE Sub-Clause (Without Array Reference): When you introduce a WHERE sub-clause that doesn't mention the array field, it's like giving a specific role to all actors who meet the criteria. For instance, SET pipes_in.width = 234 WHERE shaft_depth = 1650 tells the 'pipe in' characters to set their width to 234, but only if they're performing in a manhole survey where the shaft depth is 1650.
🔷 SET with a WHERE Sub-Clause (With Array Reference): Now, if the WHERE sub-clause includes a reference to the array field, it's like customizing roles for select actors based on their characteristics. The SET sub-clause will be enacted for every actor that fits the WHERE sub-clause's description.
In this SQL play, you're the casting director 🌟, shaping the performance based on the script you provide. With precision and care, you define the roles and determine which actors play them, making every query a unique and captivating show!
Bravo! 🎉
10.6 Interaction between ‘bare’ array fields and aggregate functions If you use array field values 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 records in the array field for which the WHERE sub-clause is true. This means that if you say SET user_number_1 = COUNT(details.*) WHERE details.code = 'DES'this will set user_number_1 to the number of details records for the CCTV survey for which the code is DES. In this case saying SET user_number_1 = COUNT(details.code=’DES’) would have the same effect. A more practical example is that to sum the service score for detail records of type DE could be done with the query SET user_number_1 = SUM(details.service_score) WHERE details.code='DE' An alternative way of calculating this without a bare array field would be SET user_number_1 = SUM(IIF(details.code='DE',details.service_score,0)) If, however, you use an aggregate function IN the WHERE sub-clause, the aggregate function will be run for all records of the array field.
🔗 10.6 The Dance of 'Bare' Array Fields and Aggregate Functions
Imagine each 'bare' array field as a solo dancer 💃 and the aggregate functions as the choreography 🕺 they follow. When you use array field values outside aggregate functions in a WHERE sub-clause, it's like setting the stage for a solo performance. Then, when you use aggregate functions in the SET sub-clause, the dance comes to life, but only for those dancers spotlighted by the WHERE clause.
So, when you command:
sqlSET user_number_1 = COUNT(details.*) WHERE details.code = 'DES'
You're essentially asking to tally up 🧮 the number of 'DES' codes within the details records, setting the user_number_1 variable with this number. It's like counting how many dancers performed a specific move.
In another scenario, if you wish to sum up the service scores 📊 for the detail records labeled 'DE', you'd go with:
sqlSET user_number_1 = SUM(details.service_score) WHERE details.code='DE'
It's like saying, "Show me the total score when all the 'DE' dancers have finished their routines."
Alternatively, if you don't want to rely solely on the 'bare' array fields, you could use:
sqlSET user_number_1 = SUM(IIF(details.code='DE', details.service_score, 0))
This is like asking for a sum of scores where 'DE' dancers add their score, and others add zero to the mix.
However, if you put an aggregate function right in the WHERE clause's spotlight, that function will sweep across all records in the array field, like a grand finale move on the dance floor.
So, in the realm of SQL, it's all about how you set the stage 🎭 and direct the performance to get the results you want from your data dancers.
Embrace this choreography of functions and fields to harmonize 🎶 your data queries with precision and flair!
