Error executing SQL statement

[tcenes]

Hallo,

 

always if i try to fill a report i get the "Error executing SQL statement" Message and i cant understand why.

net.sf.jasperreports.engine.JRException: net.sf.jasperreports.engine.JRException: Error executing SQL statement for : Reportname

at 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 : faktura

at 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 8

at 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.

 

[Boby B Jacob]

Going through the error description, it seems there is a syntactical error in your SQL code. Please check

[tcenes]

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 einzelaufstellungLEFT 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.joinidorder by [Zahlungsart] ASC[/code]

[Ankur Gupta]

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

[tcenes]

already did

[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

[tcenes]

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

[Sukanya Parisa]

Hi,I think the problem is with allias names of the columns, Give the allias names with in double quotes and check if it not works then check the right syntax to give allias names to the columns.

 

[tcenes]

Got it, the "names" dont need any kind of '', "" or []. Just the Strings need 'STRING'.

[sentoul]

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).

 

[dean871025]

for input parameter, use $P!{input_name}

[qpkorr]

Thanks sentoul - I was pretty confused but my case was the same as yours, my query was fine - and selecting "All" saved the day.

[ag33]

Wonderfull, it's so easy et simple... just "all" instead of "100 records", but i will never find this solution by myself.

Because in Netbeans (with plugin) it works perfectly with "100 records" but not in Jaspersoft ireport 5.6.0 !

Tanks a lot !