[teiid-issues] [JBoss JIRA] (TEIID-3312) Subquery field giving a group by error

[Steven Hawkins (JIRA)]

    [ https://issues.jboss.org/browse/TEIID-3312?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13035438#comment-13035438 ] 

Steven Hawkins commented on TEIID-3312:
---------------------------------------

You'd have to rely on the workaround for now.  You can add a vote for the issue to be included then in 8.10 or 8.11.

> Subquery field giving a group by error
> --------------------------------------
>
>                 Key: TEIID-3312
>                 URL: https://issues.jboss.org/browse/TEIID-3312
>             Project: Teiid
>          Issue Type: Feature Request
>            Reporter: Mark Tawk
>            Assignee: Steven Hawkins
>            Priority: Critical
>
> Hello,
> I'm using Teiid 8.9.0.Alpha2, Mysql 5.7 and mysql5 Translator
> I have query, in which i'm using a subquery field containing a criteria using a date field coming from the main query YEAR("activities"."ActivityDate").
> Teiid is forcing me to put "activities"."ActivityDate" in main query group by, while i want to group my data by YEAR("activities"."ActivityDate").
> I'm getting the following teiid error :
> TEIID30020 Processing exception for request fOwqPyy91bbL.0 'TEIID30492 [activities.ActivityDate] cannot be used outside of aggregate functions since they are not present in a GROUP BY clause.'. 
> Here is the query:
> select COUNT( distinct "activities"."ActEmpName") as "activities_ActEmpName"
> ,YEAR(activities.ActivityDate) as "activities_ActivityDate"
>     ,(select COUNT( distinct "employees_sub"."EmpFullName") as "employees_EmpFullName" 
>         from  "TrackerModel".tracker_datawarehouse."employees" "employees_sub" 
>         where  ( YEAR(employees_sub.EmpHireDate) <=  YEAR("activities"."ActivityDate"))  
>         LIMIT 0 , 1) as "SubQuery Employees"
>         from  "TrackerModel".tracker_datawarehouse."activities" "activities" 
> group by YEAR(activities.ActivityDate) 
> LIMIT 0 , 10



--
This message was sent by Atlassian JIRA
(v6.3.11#6341)