Teiid Embedded Server : Exception when execute a query

[jchidiac]

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.

[shawkins]

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