I’m an SQL neophyte and haven’t figured out the OVER / PARTITION BY / GROUP BY stuff. I’m trying to find students whose total (transport_action table) suspension days equals/exceeds a certain value, but the following generates errors:
queryText = "SELECT std_student_id, sum(trn_cit_suspension_days) FROM student";
queryText += " JOIN transport_action ON (trn_student_id = std_student_id)";
queryText += " WHERE trn_action_category = \'citation\' AND";
queryText += " trn_cit_d IS NOT NULL AND";
queryText += " trn_cit_d::date >= \'" + utils.dateFormat(citationsSince_D,"yyyy-MM-dd") + "\'";
queryText += " GROUP BY std_student_id"
queryText += " HAVING sum(trn_cit_suspension_days) >= " + suspDays_ct;
I also tried (in Terminal),
SELECT std_name_last, sum(trn_cit_suspension_days) OVER (PARTITION BY std_name_last)
FROM student JOIN transport_action ON (trn_student_id = std_student_id);
That at least generated results, but it is one row for each related many record in transport_action:
SELECT std_name_last, sum(trn_cit_suspension_days) OVER (PARTITION BY std_name_last)
FROM student JOIN transport_action ON (trn_student_id = std_student_id)
GROUP BY std_name_last;
This phrase generated the error:
ERROR: column "transport_action.trn_cit_suspension_days" must appear in the GROUP BY clause
or be used in an aggregate function
How should this be constructed to accumulate the suspension days for each student?
your initial query doesn’t look so wrong, except for escaping the single quotes.(which isn’t necessary IMHO)
Can you please post the error you got on that query?
select std_student_id from student where std_student_id in (SELECT std_student_id, sum(trn_cit_suspension_days) FROM student JOIN transport_action ON (trn_student_id = std_student_id) WHERE trn_action_category = 'citation' AND trn_cit_d IS NOT NULL AND trn_cit_d::date >= '2012-09-19' GROUP BY std_student_id HAVING sum(trn_cit_suspension_days) >= 2) and tenant_id = ? order by std_name_last asc, std_name_first asc, std_student_id asc parameters: ['C31BE623-D3AF-49DE-B244-426CD5043B81' ,type: java.lang.String]
There is a bunch of red text both above and below the query. Here is the one above the query,
org.mozilla.javascript.WrappedException: Wrapped com.servoy.j2db.dataprocessing.DataException: ERROR: subquery has too many columns
Position: 58 (/Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js#7188)
at org.mozilla.javascript.Context.throwAsScriptRuntimeEx(Context.java:1828)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:199)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:367)
at org.mozilla.javascript.Interpreter.interpretLoop(Interpreter.java:3666)
at script.browse_transport_getChanges2(/Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/browse_form.js:7188)
at script.onClick_btn_ok(/Users/djlapin/servoy_workspace_20120313/Selpa_Mgr_SQL/forms/transport_getChanges_dlg.js:168)
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:3134)
at org.mozilla.javascript.InterpretedFunction.call(InterpretedFunction.java:165)
at com.servoy.j2db.scripting.ScriptEngine.executeFunction(ScriptEngine.java:537)
at com.servoy.j2db.debug.RemoteDebugScriptEngine.executeFunction(RemoteDebugScriptEngine.java:392)
at com.servoy.j2db.FormController.executeFunction(FormController.java:4072)
at com.servoy.j2db.FormController.executeFunction(FormController.java:3951)
at com.servoy.j2db.FormController.executeFunction(FormController.java:3873)
at com.servoy.j2db.FormController$ScriptExecuter.executeFunction(FormController.java:3728)
at com.servoy.j2db.ui.BaseEventExecutor.fireEventCommand(BaseEventExecutor.java:271)
at com.servoy.j2db.ui.BaseEventExecutor.fireActionCommand(BaseEventExecutor.java:217)
at com.servoy.j2db.server.headlessclient.dataui.WebEventExecutor.onEvent(WebEventExecutor.java:412)
at com.servoy.j2db.server.headlessclient.dataui.WebEventExecutor$2.onEvent(WebEventExecutor.java:174)
at org.apache.wicket.ajax.AjaxEventBehavior.respond(AjaxEventBehavior.java:177)
at org.apache.wicket.ajax.AbstractDefaultAjaxBehavior.onRequest(AbstractDefaultAjaxBehavior.java:312)
at org.apache.wicket.request.target.component.listener.BehaviorRequestTarget.processEvents(BehaviorRequestTarget.java:157)
at org.apache.wicket.request.AbstractRequestCycleProcessor.processEvents(AbstractRequestCycleProcessor.java:92)
at org.apache.wicket.RequestCycle.processEventsAndRespond(RequestCycle.java:1279)
at org.apache.wicket.RequestCycle.step(RequestCycle.java:1358)
at org.apache.wicket.RequestCycle.steps(RequestCycle.java:1465)
at org.apache.wicket.RequestCycle.request(RequestCycle.java:545)
at org.apache.wicket.protocol.http.WicketFilter.doGet(WicketFilter.java:486)
at com.servoy.j2db.server.servlets.Zl.doGet(Zl.java:8)
at org.apache.wicket.protocol.http.WicketServlet.doGet(WicketServlet.java:138)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:617)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:554)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:859)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
at java.lang.Thread.run(Thread.java:680)
Caused by: com.servoy.j2db.dataprocessing.DataException: ERROR: subquery has too many columns
Position: 58
at com.servoy.j2db.dblayer.Zc.Za(Zc.java:310)
at com.servoy.j2db.server.Za.Zo.Za(Zo.java:1067)
at com.servoy.j2db.dataprocessing.Zv.Za(Zv.java:730)
at com.servoy.j2db.dataprocessing.Zv.Za(Zv.java:784)
at com.servoy.j2db.dataprocessing.Zv.performQuery(Zv.java:1048)
at com.servoy.j2db.debug.ProfileDataServer.performQuery(ProfileDataServer.java:284)
at com.servoy.j2db.debug.ProfileDataServer.performQuery(ProfileDataServer.java:275)
at com.servoy.j2db.dataprocessing.FoundSet.performQuery(FoundSet.java:5428)
at com.servoy.j2db.dataprocessing.FoundSet.loadByQuery(FoundSet.java:1520)
at com.servoy.j2db.dataprocessing.FoundSet.js_loadRecords(FoundSet.java:1038)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:179)
... 43 more
I’m guessing you use the query to do something like: foundset.loadRecords(queryText)
That’s where things go wrong, because the subselect (in this case the content of queryText) can only have 1 column specified.
So just drop ‘sum(trn_cit_suspension_days)’ in the SELECT part of the query and it’ll work.
But why does it work? My understanding of SQL is very limited…I thought the aggregate needed to be in the SELECT part of the sentence, in order to use it at the bottom (in the HAVING). Also, why is the first part considered the subselect?
Also, is there a way to use the Servoy “fs.find()” approach to express this same search?
You can turn your query into an instant view by wrapping it in another SELECT like so:
var queryText = "SELECT std_student_id FROM ( \
SELECT std_student_id, sum(trn_cit_suspension_days) FROM student \
JOIN transport_action ON (trn_student_id = std_student_id) \
WHERE trn_action_category = 'citation' AND \
trn_cit_d IS NOT NULL AND \
trn_cit_d::date >= to_date(?,'YYYY-MM-DD') \
GROUP BY std_student_id \
HAVING sum(trn_cit_suspension_days) >= ? \
) t";
forms.myForm.foundset.loadRecords(queryText, [utils.dateFormat(citationsSince_D,"yyyy-MM-dd"), suspDays_ct]);
This will allow your query to use the HAVING and still return only the std_student_id.
Also if you are using PostgreSQL (or Oracle) you need to pass a date or use the to_date() function to make your date STRING a DATE. If the column is a TIMESTAMP/DATETIME then you use to_timestamp().
For more info in this check http://www.postgresql.org/docs/9.1/stat … tting.html .
If it works without the extra wrap, why would I do the wrap, and what is an instant view? Also, there is a “t” at the end of queryText – what does it do?
The ‘wrapper’ is needed so you don’t get the “Subquery has too many columns” error and still get the required resultset.
I call it an instant view because it’s in fact the same as a database view. It’s just not stored in the database as such.
I.e. you can create a view using your original SQL and then query that view, that is what I am doing here but in one query.
And the t is needed because PostgreSQL requires a table/view name so in this case the name is t but can be named anything really.
Actually you are correct. You don’t need to have the SUM in the SELECT list to use it in the HAVING. So the ‘wrapper’ is not needed.
Sorry for the noise.
