Usage of array type

[tomesc]

In my translator I use the 'array type' feature. Here an example:

Table table = metadataFactory.addTable("FILE_0004");

Column column = metadataFactory.addColumn("ISN", "string", table);

Column column = metadataFactory.addColumn("DB", "string[]", table);

A certain SELECT on this table delivers the following output:

select f.ISN,f.DB,array_length(f.DB) as dblength,array_get(f.DB,1) as idx1,array_get(f.DB,2) as idx2

    from FILE_0004 f

    where f.ISN='17476297';

ISN	DB	dblength	idx1	idx2
17476297	<UnknownType (2.003)>	10	VAL1	VAL2

So far so good. But does TEIID provide any kind of sql syntax/function to build a Select which returns one line per array entry? Like:

ISN	DB	dblength	val
17476297	<UnknownType (2.003)>	10	VAL1
17476297	<UnknownType (2.003)>	10	VAL2

The ARRAYTABLE feature seems not to do that job, or?

Thanks in advance?

Thomas

[shawkins]

There is an ARRAYITERATE system procedure that can walk over the items in an array:

select f.ISN,f.cast(a.col as string),array_length(f.DB) as dblength,array_get(f.DB,1) as idx1,array_get(f.DB,2) as idx2

    from FILE_0004 f, (exec arrayiterate(DB)) a

    where f.ISN='17476297';

Unfortunately it's a little clunky to use as it's untyped and does require an explicit join.

[tomesc]

Thanks, Steven. I noticed this procedure, but I wasn't get it running. Also your proposal does not work for me:

     TEIID31119 Symbol DB is specified with an unknown group context

I tried "(exec arrayiterate(f.DB))" instead, but it results in the same error message. Currently I use the following workaround:

select f.ISN,f.DB,array_length(f.DB) as dblenth,a.idx,array_get(f.DB,a.idx) as val

    from FILE_0004 f, (select cast(x.col as integer) as idx from (exec arrayiterate((1,2))) x) a

    where f.ISN='17476297';

It works, but it isn't very smart.

[shawkins]

Sorry we need to use a lateral join to reference the DB column:

select f.ISN,f.cast(a.col as string),array_length(f.DB) as dblength,array_get(f.DB,1) as idx1,array_get(f.DB,2) as idx2

    from FILE_0004 f, table(exec arrayiterate(f.DB)) a

    where f.ISN='17476297';

[tomesc]

Great! It works.

Thank's for your help.