SQL and Array Data

There are many examples in InfoAsset Manager of a network object containing an array of data. The most obvious one is the Bends data field, which stores the intermediate (x,y) pairs that define the bends in a link object.

These arrays can take two forms:

• Value - such as Long Integer or Double contains numbers of the same type, such as the Bends data field. You cannot refer to individual elements in these arrays using SQL
• Structure - contains structured data for a sub-object. Each element in the structure has a name and you can refer to individual elements using SQL. However, an element inside a structure may be a sub-array of type Value. Again you cannot access individual values in such an array

Value Arrays

If you carry out a comparison with a value array, InfoAsset Manager will make the comparison with all the elements in the array. This is best explained with an example.

Click on the image below to reveal the example.

Example

In this example:

X = 3

Y is an array (1, 4, 5)

Comparison results would be:

X>Y is true because 3>1

X<=Y is true because 3<=4

Therefore in this example X > Y, X >= Y, X < Y and X <=Y are all true. So, for that matter, are NOT(X>Y), NOT(X>=Y).

Comparisons with value arrays are still useful as long as you understand exactly how they work. For example, if Y defines the elevation values for a river channel cross section, X<Y can be used to find values that are below the channel bed.

Structure Arrays

You can access the fields in a structure array by name and treat them in the same way as any other object field. The easiest way to do this is to use the list in the SQL Field box on the SQL Dialog to choose the data item. The syntax for fields in a structure array is array.field.

Note: Be careful not to confuse linked objects with array data as the syntax is the same - array.field and linked_object.field.