4 Replies Latest reply on Aug 3, 2017 5:19 AM by tomesc

Usage of array type

tomesc Aug 2, 2017 10:47 AM

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

1. Re: Usage of array type

shawkins Aug 2, 2017 11:31 AM (in response to tomesc)

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.
Actions
2. Re: Usage of array type

tomesc Aug 3, 2017 3:55 AM (in response to shawkins)

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.
Actions
3. Re: Usage of array type

shawkins Aug 3, 2017 4:32 AM (in response to tomesc)

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';
Actions
4. Re: Usage of array type

tomesc Aug 3, 2017 5:19 AM (in response to shawkins)

Great! It works.
Thank's for your help.
Actions

Go to original post