Query grid field in VDATA

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

You cannot query a grid field such as SELECT rating FROM VDATA. You can however query sub-fields in a grid field because although grid fields are not columns in VDATA, sub-fields are treated as columns. There are typically three kinds of grids: single response grids, multiple responses grids, and numeric grids.

Single response grids and multiple responses grids

You can use gridName[{elementName}].gridSubField to obtain a sub‑field’s value. See Return category value for more information on returning categorical values in JDBC.

Example

In the following example (based on data from the UNICOM Intelligence Developer Libraryary museum.mdd example), rating is a multiple response grid:

SELECT
rating[{Dinosaurs}].Column
, rating[{Conservation}].Column
, rating[{Fish_and_reptiles}].Column
FROM VDATA

The result data is:

rating[{Dinosaurs}].Column	rating[{Conservation}].Column	rating[{Fish_and_reptiles}].Column
{52}	{51}	{52}
{51}	{}	{50}
{48}	{}	{51}
{51}	{51}	{51}

Numeric grids

You can use gridName [{elementName}].numericName to obtain a sub-field’s value.

Example

Using data from the UNICOM Intelligence Developer Library short_drinks.mdd example:

SELECT
numdrksz[{TEA}].mond
, numdrksz[{COFFEE}].mond
, numdrksz[{TEA}].tuesd
, numdrksz[{COFFEE}].tuesd
FROM VDATA

The result data is:

numdrksz[{TEA}].mond	numdrksz[{COFFEE }].mond	numdrksz[{TEA}].tuesd	numdrksz[{COFFEE}].tuesd
5	2	4	3
3	4	3	3
6	0	6	0
0	3	0	2