Query array field in VDATA

JDBC Driver > UNICOM Intelligence JDBC Driver specification > Query from VDATA > Query array field in VDATA

If an array is unbound, it is not possible to query data from VDATA, but is it is possible to query flattened data from HDATA. You cannot query an array field such as SELECT person FROM VDATA. You can however query sub-fields in an array field because although array fields are not columns in VDATA, sub-fields are treated as columns. If the array field is a bound array, the result set is converted into multiple columns, such as:

ArrayField[M].subfield, ArrayField[M+1].subfield, ..., ArrayField[N].subfield

M is the lower-bound value and N is upper-bound value.

Example

In the following example (based on data from the UNICOM Intelligence Developer Library household.mdd example), the person field is an array that includes the age sub-field. The person field is bound from 1 to 6. To query the age field data, the query statement would be:

SELECT person[1].age
, person[2].age
, person[3].age
, person[4].age
, person[5].age
, person[6].age
FROM VDATA

The result data is:

person[1].age	person[2].age	person[3].age	person[4].age	person[5].age	person[6].age
25	(null)	(null)	(null)	(null)	(null)
45	43	15	12	(null)	(null)
72	81	(null)	(null)	(null)	(null)
32	9	2	(null)	(null)	(null)
29	31	(null)	(null)	(null)	(null)
63	(null)	(null)	(null)	(null)	(null)
38	39	8	7	4	(null)
25	23	26	(null)	(null)	(null)
42	7	(null)	(null)	(null)	(null)