net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Reportnameat com.jaspersoft.studio.editor.preview.view.control.ReportControler.fillReport(ReportControler.java:482)at com.jaspersoft.studio.editor.preview.view.control.ReportControler.access$18(ReportControler.java:457)at com.jaspersoft.studio.editor.preview.view.control.ReportControler$4.run(ReportControler.java:347)at org.eclipse.core.internal.jobs.Worker.run(Worker.java:54)Caused by: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : fakturaat net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:240)at net.sf.jasperreports.engine.fill.JRFillDataset.createQueryDatasource(JRFillDataset.java:1112)at net.sf.jasperreports.engine.fill.JRFillDataset.initDatasource(JRFillDataset.java:689)at net.sf.jasperreports.engine.fill.JRBaseFiller.setParameters(JRBaseFiller.java:1281)at net.sf.jasperreports.engine.fill.JRBaseFiller.fill(JRBaseFiller.java:900)at net.sf.jasperreports.engine.fill.BaseFillHandle$ReportFiller.run(BaseFillHandle.java:120)at java.lang.Thread.run(Unknown Source)Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Zahlungsart],einzelaufstellung.Anzahl as [Anzahl der Aufträge],einzelaufstel' at line 8at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)at java.lang.reflect.Constructor.newInstance(Unknown Source)at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)at com.mysql.jdbc.Util.getInstance(Util.java:386)at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4237)at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4169)at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2617)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2778)at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2825)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2156)at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2323)at net.sf.jasperreports.engine.query.JRJdbcQueryExecuter.createDatasource(JRJdbcQueryExecuter.java:233)... 6 more
Thanks for your Help.
10 Answers:
Hmm i'm not sure what can be wrong.
SELECT CASE WHEN einzelaufstellung.Zahlungsart = 'banktransfer' THEN 'Lastschrift' WHEN einzelaufstellung.Zahlungsart = 'eustandardtransfer' THEN 'Vorkasse' WHEN einzelaufstellung.Zahlungsart = 'invoice' THEN 'Rechnung' WHEN einzelaufstellung.Zahlungsart = 'paypal' THEN 'Paypal' WHEN einzelaufstellung.Zahlungsart = 'paypalexpress' THEN 'Paypalexpress' END AS [Zahlungsart], einzelaufstellung.Anzahl AS [Anzahl der Aufträge], einzelaufstellung.Anzahl/gesamtzahl.Anzahl*100 AS [Anteil der Aufträge IN %], einzelaufstellung.Umsatz AS [Umsatz IN EUR], einzelaufstellung.Umsatz/gesamtzahl.Umsatz*100 AS [Anteil des Umsatzes IN %] FROM ( SELECT '1' AS [joinid], o.payment_method AS [Zahlungsart], COUNT(o.orders_id) AS [Anzahl], SUM(ot.value) AS [Umsatz] FROM orders AS o LEFT OUTER JOIN orders_total AS ot ON o.orders_id = ot.orders_id AND class = "ot_total" LEFT OUTER JOIN customers AS c ON o.customers_id=c.customers_id GROUP BY o.payment_method ) AS einzelaufstellung LEFT OUTER JOIN ( SELECT '1' AS [joinid], COUNT(o.orders_id) AS [Anzahl], SUM(ot.value) AS [Umsatz] FROM orders AS o LEFT OUTER JOIN orders_total AS ot ON o.orders_id = ot.orders_id AND class = "ot_total" ) AS gesamtzahl ON einzelaufstellung.joinid = gesamtzahl.joinid ORDER BY [Zahlungsart] ASC
Hi,
The error stack trace clearly demarcates that there is problem in SQL --
"Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; "
Please send the query so that could have a look and resolve you issue.
Thanks,
Ankur Gupta
Hi, I think the problem is in the CASE-WHEN: The default syntax is -
CASE case-expression
WHEN when-expression-1 THEN value-1
[ WHEN when-expression-n THEN value-n ... ]
[ ELSE else-value ]
END
In your query you have missed the else value, which I think is giving error try changing the last When to else that might help like
CASE WHEN einzelaufstellung.Zahlungsart = 'banktransfer' THEN 'Lastschrift'
WHEN einzelaufstellung.Zahlungsart = 'eustandardtransfer' THEN 'Vorkasse'
WHEN einzelaufstellung.Zahlungsart = 'invoice' THEN 'Rechnung'
WHEN einzelaufstellung.Zahlungsart = 'paypal' THEN 'Paypal'
ELSE 'Paypalexpress'
END AS [Zahlungsart]
Please let me know if it helps.
Thanks,
Ankur Gupta
Hi,
i put in the Else-Value but theres always the error message again.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[Zahlungsart],einzelaufstellung.Anzahl as [Anzahl der Aufträge],einzelaufst' at line 7
I met with this problem and the SQL actually ran OK when run in directly on MySQL.
In my case, the problem solved completely by selecing "All" instead of the default option "First 100 records" in the Preview Data section (bottom section in the Report query window).
already did