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

    Usage of array type

    Thomas Esche Newbie

      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';

       

      ISNDBdblengthidx1idx2
      17476297<UnknownType (2.003)>10VAL1VAL2

       

      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:

       

      ISNDBdblengthval
      17476297<UnknownType (2.003)>10VAL1
      17476297<UnknownType (2.003)>10VAL2

       

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

       

      Thanks in advance?

      Thomas

        • 1. Re: Usage of array type
          Steven Hawkins Master

          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.

          • 2. Re: Usage of array type
            Thomas Esche Newbie

            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.

            • 3. Re: Usage of array type
              Steven Hawkins Master

              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';

              • 4. Re: Usage of array type
                Thomas Esche Newbie

                Great! It works.

                Thank's for your help.