7 Replies Latest reply on Oct 18, 2013 4:25 PM by shawkins

    Order by query in TEIID

    teiid123

      Hi,

           The ORDER BY is not working correctly.Though i specified  ORDER BY ENAME in my query the teiid igonres and always executed with first column selection (ECODE).

       

                      select ECODE,ENAME from EMPLOYEE ORDER BY ENAME

       

       

      How to display the employee list  order by ENAME? why my  query executed always  with order by  first column? do i need to do any changes in my query?

      your help could be appreci

        • 1. Re: Order by query in TEIID
          shawkins

          What version are you on? Can you provide a query plan?

           

          Steve

          • 2. Re: Order by query in TEIID
            teiid123

            Hi Steve,

                        Here is my detail question with example.

             

            Teiid version  : 8.2.0 and Oracle Database

            vdb Name  : TEST.VDB

            Base Table Name  : FUND_REFERENCE

            Query Under Base Table in tediid designer editor : SELECT F.FUND_CODE, F.FUND_NAME FROM FUND AS F ORDER BY F.FUND_NAME

             

            Deployed this VDB in Jboss server and tried to test from squirrel.


                Select * from  FUND_REFERENCE

             

            Query plan which is from teiid log file
            -----------------------------------------

            SELECT DISTINCT g_0.FUND_CODE AS c_0, g_0.FUND_NAME AS c_1 FROM FUND AS g_0


            I don't find  ORDER BY FUND_NAME in my query plan. Why teiid ignores ORDER BY in my case?  please

             

             

            Thanks

            Lawrence

            • 3. Re: Order by query in TEIID
              shawkins

              Teiid, just like dbs like SQL Server, don't consider tables/views to have an ordering.  This allows for greater flexibility in optimizing.  So if you put just an order by in view the optimizer will simply remove it.  You can use a limit to force the ordering (such as limit 2147483647) or do your select as part of a procedure, or ideally add the desired ordering to your user query.

               

              Steve

              • 4. Re: Order by query in TEIID
                teiid123

                Hi Steve,

                           It worked for me and thank you very much for your help and support. I Used Limit feature.

                 

                 

                 

                Thanks

                Lawrence

                • 5. Re: Order by query in TEIID
                  shawkins

                  Just keep in mind that using a limited order by in a view will force that possibly expensive sort operation to take place even if you use your view in joins, unions, etc.

                  • 6. Re: Order by query in TEIID
                    teiid123


                    Hi Steve,

                           Is it good if i use throug procedure as below

                     

                    CREATE VIRTUAL PROCEDURE
                    BEGIN
                    SELECT DISTINCT FUND_CODE, FUND_NAME FROM FUND order by FUND_NAME;
                    END

                     

                     

                     

                    Thanks

                    Lawrence

                    • 7. Re: Order by query in TEIID
                      shawkins

                      Yes, that is fine as long as your client doesn't mind calling through a stored procedure.