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.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.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.