1 Reply Latest reply on Sep 29, 2014 10:41 AM by Steven Hawkins

    Teiid Embedded Server : Exception when execute a query

    Joseph CHIDIAC Novice

      Hi, I'm using Teiid Embedded Server 8.8 version

       

      when  i execute the following query :

       

      SELECT (  COUNT(Orders.ID)

              / (SELECT COUNT(OrderLines_sub.ID) AS OrderLines_ID

                   FROM OrdersModel.Orders.OrderLines OrderLines_sub

                  WHERE (WEEK(OrderLines_sub.ShipDate) = WEEK(Orders.OrderDate))

                  LIMIT 0, 1))

                AS CalculatedField,

             WEEK(Orders.OrderDate) AS Orders_OrderDate

        FROM OrdersModel.Orders.Orders Orders

      GROUP BY WEEK(Orders.OrderDate)

       

      i got the following error :

       

      WARNING: TEIID30020 Processing exception for request kXXnbsptr5hg.

      0 'TEIID30492 [Orders.OrderDate] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.'.

      Originally QueryValidatorException Request.java:305.

      Enable more detailed logging to see the entire stacktrace.

        • 1. Re: Teiid Embedded Server : Exception when execute a query
          Steven Hawkins Master

          Correlated aggregate/grouping expressions are not yet supported.  You would have to use a subquery to workaround to provide a simple column reference:

           

          select id_count/(SELECT COUNT(OrderLines_sub.ID) AS OrderLines_ID

                       FROM OrdersModel.Orders.OrderLines OrderLines_sub

                      WHERE (WEEK(OrderLines_sub.ShipDate) = order_week

                      LIMIT 0, 1))

                    AS CalculatedField, order_week from (SELECT COUNT(Orders.ID) as id_count, WEEK(Orders.OrderDate) as order_week from OrdersModel.Orders.Orders Orders GROUP BY WEEK(Orders.OrderDate)) as v