5 Replies Latest reply on Jul 24, 2013 8:07 PM by Steven Hawkins

    LIMIT in virtual procedure

    luca gioppo Novice

      I'm trying to apply a limit clause to a virtual procediure with no luck

       

      CREATE VIRTUAL PROCEDURE

      BEGIN

                  SELECT XMLELEMENT(NAME alberate, XMLAGG(XMLELEMENT(NAME alberata, XMLFOREST(AlberataView.AlberataV.WKT_GEOM, AlberataView.AlberataV.ST_P_PIA, AlberataView.AlberataV.AMBITO, AlberataView.AlberataV.PAVIMENTAZIONE, AlberataView.AlberataV.POSIZIONE, AlberataView.AlberataV.SPECIE, AlberataView.AlberataV.ALTEZZA, AlberataView.AlberataV.TRONCO, AlberataView.AlberataV.ETA)))) AS result FROM AlberataView.AlberataV LIMIT AlberataView.AlberataRest."limit";

      END

       

       

      Where AlberataView.AlberataRest."limit" is an input parameter of the procdure defined as integer.

       

      I get this error:

       

      The query is not parsable.

      TEIID31100 Parsing error: Encountered "FROM AlberataView.AlberataV LIMIT [*]AlberataView.AlberataRest.\"limit\"[*]; END" at line 5, column 432.

      Was expecting: <UNSIGNEDINTEGER> | "?"

       

      Any hint?

        • 1. Re: LIMIT in virtual procedure
          luca gioppo Novice

          TEIID Designer version 8.1 beta

          • 2. Re: LIMIT in virtual procedure
            Steven Hawkins Master

            Logically that should be allowed as it's functionally the same as allowing a prepared parameter.  An enhancement would be needed though as the parser is not looking for a variable reference.  Can you log something?

             

            The workaround would be to use a dynamic query.

             

            Steve

            • 3. Re: LIMIT in virtual procedure
              Steven Hawkins Master

              I went ahead and worked this in 8.5 as https://issues.jboss.org/browse/TEIID-2595

               

              Thanks,

              Steve

              • 4. Re: LIMIT in virtual procedure
                luca gioppo Novice

                Thanks Steven,

                does this also affect designer and the validation at transformation editor level?

                At which nightly build will it be merged?

                 

                Also can you shed an example of dynamic query with LIMIT?

                Also since I want to implement a REWST api pagination using the LIMIT and the offset as REST parameters and I want to generate the rest war using Designer I could generate the WAR with the procedure without LIMIT (so I do not get errors and the generator works) and than creating the dyamic query with the same name and substitute the VDB at deployment time?

                 

                Thanks

                 

                Luca

                • 5. Re: LIMIT in virtual procedure
                  Steven Hawkins Master

                  > does this also affect designer and the validation at transformation editor level?

                   

                  Yes, Designer has to pick up the corresponding engine bits to support the language features at runtime.

                   

                  > At which nightly build will it be merged?

                   

                  I can't really say.  It will be a while before they pick up an 8.5 build and I don't think there will be a community release against Teiid 8.4.1.

                   

                  > Also can you shed an example of dynamic query with LIMIT?

                   

                  See https://docs.jboss.org/author/display/TEIID/Procedure+Language

                   

                  EXECUTE IMMEDIATE 'SELECT XMLELEMENT(NAME alberate, ... LIMIT' || AlberataView.AlberataRest."limit" AS COL XML;

                   

                  > Also since I want to implement a REWST api pagination using the LIMIT and the offset as REST parameters

                   

                  The OData layer uses result caching for more efficent paging.  Such that at a user scope for a specified time limit the entire result is cached and subsets are returned as the pages.  Adding a cache hint to a query within a procedure however isn't a supported use of the cache hint.  But an enhancement would be warrented for situations like this.