Error With aggregate

Zuke · December 17, 2009, 6:05pm

Hi

I am currently building a function which needs to read a boolean field from a form, and depending on the value returned show or hide some records but keep any filtering already applied.

	var vwherezero = forms.fabman_frm_budgeting_report_setup.br_skipyrzero
	if (vwherezero == 1){
		var $sql = databaseManager.getSQL(forms.zsystem_tci_fabman_detail.foundset)
		var $year_zero_query = "SELECT budget_schedules.fc_fabric_key " +
							   "FROM budget_schedules, fabric_condition "+
							   "WHERE fabric_condition.fc_program_year_basic IS NOT NULL "+
							   "AND fabric_condition.fc_program_year_basic !=0 "+
							   "AND budget_schedules.fc_fabric_key = fabric_condition.fc_id "+
							   "AND fabric_condition.fc_id IN (" + $sql + ")"	
		controller.loadRecords($year_zero_query)
		}
		else
		{
		var $year_zero_query = "SELECT fc_fabric_key FROM budget_schedules"	
			controller.loadRecords($year_zero_query)
		}

So where vwherezero = 1 i want to hide any records which have either 0 or null as a value for fc_program_year_basic.
I use getsql to get the sql code for a form i know has filters applied and concatenate it into a where clause for my new pk query, and then do loadRecords($year_zero_query).

When the code is executed i get the following error

There is a problem with an aggregate
 > com.servoy.j2db.dataprocessing.DataException: 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 'and budget_schedules.fc_fabric_key = budgetschedules22743.fc_fabric_key) limit 2' at line 1

I do have aggregates within my solution, but all work fine except when i try to apply the above code.
Im not really sure what the line “budgetschedules22743” is referencing as i dont have that anywhere within my solution as far as i can tell.

Any help would be much appreciated
Regards

rgansevles · December 18, 2009, 7:38am

McCourt,

What is the value of $sql when the error occurs?
You can try the same query in a db tool directly in the db and see why it is an issue.

Rob

birgit · December 18, 2009, 7:59am

Hi

May missing semicolons be a problem?

Regards
Birgit

Zuke · December 18, 2009, 10:43am

Hi

I have tested the resulting sql within navicat and it returns a valid result. So im at a loss as to what is happening.

Regards

ROCLASI · December 18, 2009, 11:02am

Hi McCourt,

Like Rob already asked, what’s the value of $sql ?
Does it contain a semicolon at the end of the $sql value for example?

Zuke · December 18, 2009, 11:10am

Hi all

For your info if i output the built SQL string to console i get the following

SELECT budget_schedules.fc_fabric_key FROM budget_schedules, fabric_condition WHERE fabric_condition.fc_program_year_basic IS NOT NULL AND fabric_condition.fc_program_year_basic !=0 AND budget_schedules.fc_fabric_key = fabric_condition.fc_id AND fabric_condition.fc_id IN (select fc_id from fabric_condition  order by fc_id asc)

and if i run this within navicat i get the following result

Regards

ROCLASI · December 18, 2009, 11:31am

Hi McCourt,

This is the same query that causes the error ?
I see no filter being applied (SELECT fc_id FROM fabric_condition).

I am not sure if you can check out the aggregate query in the Performance Data page (in Servoy-admin pages) since it errors. But you could check that to see what exact SQL is used for the aggregate.
If it’s not there then you need to set the logging level to debug which will result in a WHOLE load of information with among it the aggregate query.

Zuke · December 18, 2009, 11:45am

Hi

Yes this is the sql that seems to be causing the error as the form previews with aggregates and calculations fine without it.

I have a load of errors relating to aggregates within the server log when i try to print preview my form
here is an example

2009-12-18 11:39	AWT-EventQueue-0	ERROR	com.servoy.j2db.util.Debug	select sum(budgetschedules1529.bs_surface_prep_time) as bs_surface_prep_time_calc, sum(budgetschedules1529.bs_pre_treatment_cost) as bs_pre_treatment_calc, sum(budgetschedules1529.bs_paint_calculated_time) as bs_paint_time_calc, sum(budgetschedules1529.bs_m2) as m2_area_agg, sum(budgetschedules1529.bs_paint_material_cost) as bs_paint_material_cost_agg, sum(budgetschedules1529.bs_insulation_time) as direct_ins_time_agg, sum(budgetschedules1529.bs_surface_prep_mat_cost) as bs_surface_prep_mat_cost_agg, sum(budgetschedules1529.bs_insulation_material_cost) as bs_insulation_material_cost_agg, sum(budgetschedules1529.bs_pre_treat_mat_cost) as bs_pre_treat_mat_cost_agg from budget_schedules budgetschedules1529 where exists (select 1 from fabric_condition , budget_schedules where fabric_condition.fc_program_year_basic IS NOT NULL AND fabric_condition.fc_program_year_basic !=0 AND budget_schedules.fc_fabric_key = fabric_condition.fc_id AND fabric_condition.fc_id IN (select fc_id from fabric_condition and budget_schedules.fc_fabric_key = budgetschedules1529.fc_fabric_key) limit ? parameters: [2 ,type: java.lang.Integer]
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 'and budget_schedules.fc_fabric_key = budgetschedules1529.fc_fabric_key) limit 2' at line 1 
    	at sun.reflect.GeneratedConstructorAccessor33.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:406) 
    	at com.mysql.jdbc.Util.getInstance(Util.java:381) 
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) 
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) 
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536) 
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468) 
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957) 
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107) 
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648) 
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086) 
    	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2237) 
    	at sun.reflect.GeneratedMethodAccessor453.invoke(Unknown Source) 
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
    	at java.lang.reflect.Method.invoke(Unknown Source) 
    	at com.servoy.j2db.persistence.datasource.Zf.invoke(Zf.java:8) 
    	at $Proxy2.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.performSelect(SQLEngine.java:1042) 
    	at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:691) 
    	at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:239) 
    	at com.servoy.j2db.dataprocessing.FoundSet.Zb(FoundSet.java:116) 
    	at com.servoy.j2db.dataprocessing.FoundSet.Za(FoundSet.java:552) 
    	at com.servoy.j2db.dataprocessing.FoundSet.getDataProviderValue(FoundSet.java:272) 
    	at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:143) 
    	at com.servoy.j2db.dataprocessing.Zib.setRecord(Zib.java:182) 
    	at com.servoy.j2db.dataprocessing.Zgb.Za(Zgb.java:297) 
    	at com.servoy.j2db.dataui.Zze.refreshRecord(Zze.java:194) 
    	at com.servoy.j2db.Zpb.Za(Zpb.java:200) 
    	at com.servoy.j2db.Zpb.valueChanged(Zpb.java:344) 
    	at com.servoy.j2db.Zpb.Za(Zpb.java:687) 
    	at com.servoy.j2db.Zwb.Za(Zwb.java:316) 
    	at com.servoy.j2db.Zwb.Ze(Zwb.java:146) 
    	at com.servoy.j2db.Zgb.run(Zgb.java:2) 
    	at java.awt.event.InvocationEvent.dispatch(Unknown Source) 
    	at java.awt.EventQueue.dispatchEvent(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEvents(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEvents(Unknown Source) 
    	at java.awt.EventDispatchThread.run(Unknown Source)

Regards

ROCLASI · December 18, 2009, 11:59am

Hi McCourt,

This is your offending query:

SELECT SUM(budgetschedules1529.bs_surface_prep_time) as bs_surface_prep_time_calc, 
SUM(budgetschedules1529.bs_pre_treatment_cost) as bs_pre_treatment_calc, 
SUM(budgetschedules1529.bs_paint_calculated_time) as bs_paint_time_calc, 
SUM(budgetschedules1529.bs_m2) as m2_area_agg, 
SUM(budgetschedules1529.bs_paint_material_cost) as bs_paint_material_cost_agg, 
SUM(budgetschedules1529.bs_insulation_time) as direct_ins_time_agg, 
SUM(budgetschedules1529.bs_surface_prep_mat_cost) as bs_surface_prep_mat_cost_agg, 
SUM(budgetschedules1529.bs_insulation_material_cost) as bs_insulation_material_cost_agg,
SUM(budgetschedules1529.bs_pre_treat_mat_cost) as bs_pre_treat_mat_cost_agg 
FROM budget_schedules budgetschedules1529 
WHERE 
EXISTS (
	SELECT 1 FROM fabric_condition, budget_schedules 
	WHERE fabric_condition.fc_program_year_basic IS NOT NULL 
	AND fabric_condition.fc_program_year_basic !=0 
	AND budget_schedules.fc_fabric_key = fabric_condition.fc_id 
	AND fabric_condition.fc_id IN (select fc_id from fabric_condition and budget_schedules.fc_fabric_key = budgetschedules1529.fc_fabric_key
)
LIMIT 2

This looks correct to me. Perhaps MySQL can’t handle such a join ?
What do you get when you run this in Navicat ?

Zuke · December 18, 2009, 12:01pm

Hi

budgetschedules1529 doesnt exist in my db so i dont know where this is coming from???

Regards

ROCLASI · December 18, 2009, 12:04pm

Hi McCourt,

budgetschedules1529 is an alias for the table budget_schedules.
As you can see in the FROM part of the query:

FROM budget_schedules budgetschedules1529

Zuke · December 18, 2009, 12:11pm

Hi

Yes i see that now

The error i get from sql when i try to run this query is as follows

ROCLASI · December 18, 2009, 12:16pm

Hmm…I see I missed a syntax error there.

fabric_condition.fc_id IN (SELECT fc_id FROM fabric_condition AND budget_schedules.fc_fabric_key = budgetschedules1529.fc_fabric_key

SELECT fc_id FROM fabric_condition

So far so good…then you get an AND instead of a WHERE…this is wrong.
And I think the tablename should’ve been aliased as well since it’s also used in the SELECT 1 FROM part of the query.

This is something Servoy needs to address.

Zuke · December 18, 2009, 12:23pm

Hi

This error seems to be firing every time Servoy tries to perform an aggregate when controller.loadrecords() is fired with a query.

Could we have some Servoy input on this issue?
Is there any immediate workaround to this issue?

Obviously we are hitting the weekend and xmas so a promt response would be much appreciated.

Regards

ROCLASI · December 18, 2009, 12:28pm

Hi McCourt,

I suggest you (also) file a case for this.

ROCLASI · December 18, 2009, 12:56pm

Hi McCourt,

Can you try the following code?

var vwherezero = forms.fabman_frm_budgeting_report_setup.br_skipyrzero;
if( vwherezero == 1 ) {
    var $sql = databaseManager.getSQL(forms.zsystem_tci_fabman_detail.foundset);
    var $year_zero_query = "SELECT a.fc_fabric_key " +
        "FROM budget_schedules a JOIN fabric_condition b "+
        "ON a.fc_fabric_key = b.fc_id " +
        "WHERE b.fc_program_year_basic IS NOT NULL "+
        "AND b.fc_program_year_basic !=0 "+
        "AND b.fc_id IN (" + $sql + ")";
    controller.loadRecords($year_zero_query);
} else {
    var $year_zero_query = "SELECT fc_fabric_key FROM budget_schedules";
    controller.loadRecords($year_zero_query);
}

Perhaps this solves the issue you have.

Zuke · December 18, 2009, 1:12pm

Hi

I receive the following from server logs

2009-12-18 13:07	AWT-EventQueue-0	ERROR	com.servoy.j2db.util.Debug	select budgetschedules4564.fc_fabric_key from budget_schedules budgetschedules4564 inner join fabric_condition fabriccondition4565 on budgetschedules4564.fc_fabric_key=fabriccondition4565.fc_id where budgetschedules4564.fc_fabric_key in (SELECT a.fc_fabric_key FROM budget_schedules a JOIN fabric_condition b ON a.fc_fabric_key = b.fc_id WHERE b.fc_program_year_basic IS NOT NULL AND b.fc_program_year_basic !=0 AND b.fc_id IN (select fc_id from fabric_condition ) order by fabriccondition4565.cd_complex asc, fabriccondition4565.cd_plant asc, fabriccondition4565.fc_program_year_basic asc parameters: <null>
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 '' at line 1 
    	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:406) 
    	at com.mysql.jdbc.Util.getInstance(Util.java:381) 
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1030) 
    	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956) 
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3536) 
    	at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3468) 
    	at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1957) 
    	at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2107) 
    	at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2648) 
    	at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2086) 
    	at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2237) 
    	at sun.reflect.GeneratedMethodAccessor453.invoke(Unknown Source) 
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
    	at java.lang.reflect.Method.invoke(Unknown Source) 
    	at com.servoy.j2db.persistence.datasource.Zf.invoke(Zf.java:8) 
    	at $Proxy2.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.performSelect(SQLEngine.java:1042) 
    	at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:691) 
    	at com.servoy.j2db.dataprocessing.SQLEngine.performQuery(SQLEngine.java:239) 
    	at com.servoy.j2db.dataprocessing.FoundSet.loadByQuery(FoundSet.java:992) 
    	at com.servoy.j2db.Zpb.Za(Zpb.java:644) 
    	at com.servoy.j2db.FormController$JSForm.js_loadRecords(FormController.java:167) 
    	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) 
    	at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) 
    	at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) 
    	at java.lang.reflect.Method.invoke(Unknown Source) 
    	at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:179) 
    	at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:353) 
    	at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3666) 
    	at org.mozilla.javascript.Interpreter.interpret(Interpreter.java:2680) 
    	at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:166) 
    	at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:387) 
    	at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3127) 
    	at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:165) 
    	at com.servoy.j2db.scripting.Zsb.executeFunction(Zsb.java:74) 
    	at com.servoy.j2db.scripting.Ztb.executeFunction(Ztb.java:116) 
    	at com.servoy.j2db.Zpb.Za(Zpb.java:1007) 
    	at com.servoy.j2db.Zpb.Za(Zpb.java:636) 
    	at com.servoy.j2db.Zpb.ZK(Zpb.java:852) 
    	at com.servoy.j2db.Zwb.Za(Zwb.java:22) 
    	at com.servoy.j2db.Zwb.Ze(Zwb.java:146) 
    	at com.servoy.j2db.Zgb.run(Zgb.java:2) 
    	at java.awt.event.InvocationEvent.dispatch(Unknown Source) 
    	at java.awt.EventQueue.dispatchEvent(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEvents(Unknown Source) 
    	at java.awt.EventDispatchThread.pumpEvents(Unknown Source) 
    	at java.awt.EventDispatchThread.run(Unknown Source)

and also an error msg from navicat asking me to check my sql

Regards

ROCLASI · December 18, 2009, 1:24pm

Hi McCourt,

As you can see the SQL is missing a closing parenthesis. Do you know where this SQL is generated ?

SELECT budgetschedules4564.fc_fabric_key 
FROM budget_schedules budgetschedules4564 
INNER JOIN fabric_condition fabriccondition4565 ON budgetschedules4564.fc_fabric_key=fabriccondition4565.fc_id 
WHERE budgetschedules4564.fc_fabric_key IN 
(
    SELECT a.fc_fabric_key 
    FROM budget_schedules a 
    JOIN fabric_condition b ON a.fc_fabric_key = b.fc_id 
    WHERE b.fc_program_year_basic IS NOT NULL 
    AND b.fc_program_year_basic !=0 
    AND b.fc_id IN 
    (
        SELECT fc_id FROM fabric_condition 
    ) 
    ORDER BY 
    fabriccondition4565.cd_complex ASC,
    fabriccondition4565.cd_plant ASC,
    fabriccondition4565.fc_program_year_basic ASC

rgansevles · December 18, 2009, 1:50pm

McCourt,

Do you have any table or foundset filters on these tables anywhere in your solution?

Rob

Zuke · December 18, 2009, 2:11pm

Hi

No we dont at present but may have in future.

Regards

Topic		Replies	Views
loadRecords(sql) returns different data to SQL Code?? Classic Servoy	38	19947	August 27, 2010
GetDatasetByQuery and ANSI/92 Syntax not Working? Classic Servoy	7	4413	November 21, 2009
Aggregate function syntax Classic Servoy	13	5528	September 24, 2012
Simple 'loadRecords' failing with... Classic Servoy	22	17783	April 13, 2010
Viewing the SQL query results in horizontal format. Classic Servoy	41	23350	July 24, 2007

Error With aggregate

Related topics