Cannot create a sub summary on more than one field

Classic Servoy
1

I was trying to create a report with a subsummary. That subsummary requires the use of 3 fields to group the data by.

So, I added the subsummary ‘part’ to the form, added the required fields to group by and added those same fields as the sort option for the report.

However, i got a message stating that the report’s print preview could not be generated due to a syntax error. Observing the servoy log file, i noticed that the fields in the group by clause of the servoy-created query were not separated by comma’s.

This happened with Servoy 3.5 rc 3 on windows xp, using the bundled sybase 9 as the repository, and a sybase 8 db as the data store.
Here is the excerpt from the log file.

2007-07-04 15:27:18,062 ERROR [TaskExecuter[0]] com.servoy.j2db.util.Debug - select Item_Id, Entry_Type, count(Empl_Id) as RecordCount, sum(cast(Amount as numeric(19,0))) as TotalAmount from CycleChanges where CycleChanges.Pay_Group = ‘MBW’ AND CycleChanges.Cycle_No = 6 and CycleChanges.Pay_Year = 2007 group by Item_Id Entry_Type order by Item_Id asc, Entry_Type asc
com.sybase.jdbc3.jdbc.SybSQLException: ASA Error -131: Syntax error near ‘Entry_Type’ on line 1
at com.sybase.jdbc3.tds.Tds.a(Unknown Source)
at com.sybase.jdbc3.tds.Tds.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.ResultGetter.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.nextResult(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.queryLoop(Unknown Source)
at com.sybase.jdbc3.jdbc.SybStatement.executeQuery(Unknown Source)
at com.sybase.jdbc3.jdbc.SybPreparedStatement.executeQuery(Unknown Source)
at sun.reflect.GeneratedMethodAccessor149.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at com.servoy.j2db.persistence.datasource.p.invoke(Unknown Source)
at $Proxy1.executeQuery(Unknown Source)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:179)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(Unknown Source)
at com.servoy.j2db.b.i.a(Unknown Source)
at com.servoy.j2db.b.g.try(Unknown Source)
at com.servoy.j2db.b.c$1.run(Unknown Source)
at com.servoy.j2db.util.am.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)
2007-07-04 15:27:18,062 ERROR [TaskExecuter[0]] com.servoy.j2db.util.Debug - Throwable
com.servoy.j2db.dataprocessing.DataException: ASA Error -131: Syntax error near ‘Entry_Type’ on line 1
at com.servoy.j2db.dblayer.DBDependentHandler.a(Unknown Source)
at com.servoy.j2db.persistence.Server.translateSQLException(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.a(Unknown Source)
at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(Unknown Source)
at com.servoy.j2db.b.i.a(Unknown Source)
at com.servoy.j2db.b.g.try(Unknown Source)
at com.servoy.j2db.b.c$1.run(Unknown Source)
at com.servoy.j2db.util.am.run(Unknown Source)
at java.lang.Thread.run(Unknown Source)

2

There seems to be a bug in the SQL creation logic. A comma is missing in the group by clause:

group by Item_Id Entry_Type

Shouldn’t be hard to fix :D. Could you add that to the support system so I will be noticed?

3

i noticed that the fields in the group by clause of the servoy-created query were not separated by comma’s.

I guess you found that out, too :oops:

4

Patrick,

so, i posted the issue to the support system. Unfortunately, i had to post under my boss’s username (gregoryf) since i don’t seem to have access under my own login.

Anyway, thanks for the speedy reply.

5

This has been fixed in upcoming release 3.5RC5.

Rob