Failed to prepare the query execution for the data set: OrdersDataSet

[micahm90]

I am trying to reports and I get the following error. I think there is a SQL problem in the report. Can anyone help?

Chart Orders in the Last 25 Days (Chart Only):
+ An exception occurred during processing. Please see the following message for details:
Failed to prepare the query execution for the data set: OrdersDataSet
Cannot get the result set metadata.
    org.eclipse.birt.report.data.oda.jdbc.JDBCException: SQL statement does not return a ResultSet object.
SQL error #1:Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ords.day_number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
;
    com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ords.day_number' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

[Brian]

It would appear that you have ONLY_FULL_GROUP_BY enabled on your MySQL database.

One quick solution is to disable that.


Another solution is to add day_number to the group by clause...

So your query in the BIRT report would become:

this.queryText = "SELECT ords.day_number, DATE_FORMAT(DATE_SUB(curdate(), INTERVAL id DAY),'%c') month_number, DATE_FORMAT(DATE_SUB(curdate(), INTERVAL id DAY),'%b') month_name,     DATE_FORMAT(DATE_SUB(curdate(), INTERVAL id DAY),'%y') year_number, concat(DATE_FORMAT(DATE_SUB(curdate(), INTERVAL id DAY),'%e'), '-', DATE_FORMAT(DATE_SUB(curdate(), INTERVAL id DAY),'%b')) ddmon, id, count(day_number) order_count FROM utility left outer join (select DATE_FORMAT(date_purchased,'%e') day_number from orders where " + storeWhereAnd + " DATE_SUB(curdate(), INTERVAL 26 DAY) < date_purchased) ords on DATE_FORMAT(DATE_SUB(curdate(), INTERVAL id DAY),'%e') = day_number where id >0 and id < 26 group by id, day_number order by id desc";