The direction would be part of the EOSortOrdering for that property. From: Theodore Petrosky <tedp...@yahoo.com<mailto:tedp...@yahoo.com>> Date: Tuesday, July 14, 2015 at 12:20 PM To: Chuck Hill <ch...@gevityinc.com<mailto:ch...@gevityinc.com>> Cc: Oscar González <racso...@hotmail.com<mailto:racso...@hotmail.com>>, WebObjects-Dev <webobjects-dev@lists.apple.com<mailto:webobjects-dev@lists.apple.com>> Subject: Re: EOFetchSpecification - sorting by partial date
wow this is great! and I even understand the ‘Derived’ selection now. I still don’t see how to set the sort direction. Oscar’s example of the SQL it generates shows an order by of ASC. what if I want DESC? enquiring minds need this stuff!!! On Jul 14, 2015, at 1:46 PM, Chuck Hill <ch...@gevityinc.com<mailto:ch...@gevityinc.com>> wrote: Maybe I was right the first time. :-) I know that I have used this before, somewhere. So Calven, try something like this (remove the Read Format) and make it Read Only. <Entity_Modeler_-_CadreCore_CadreCore_eomodelgroup_-_Eclipse_SDK_-__Users_chuck_Documents_workspace[1].png> Chuck On 2015-07-14, 10:01 AM, "Oscar González" wrote: Hi all, I used a derived column like this (EXTRACT(YEAR FROM fechaIngreso) * 100 + EXTRACT(MONTH FROM fechaIngreso)) and it generates this sql SELECT (EXTRACT(YEAR FROM t0.fechaingreso) * 100 + EXTRACT(MONTH FROM t0.fechaingreso)) FROM ep_empxcom t0 ORDER BY (EXTRACT(YEAR FROM t0.fechaingreso) * 100 + EXTRACT(MONTH FROM t0.fechaingreso)) ASC It works with postgres, I think it will work in oracle too. Saludos, Oscar. ________________________________ Subject: Re: EOFetchSpecification - sorting by partial date From: cal...@mac.com<mailto:cal...@mac.com> Date: Tue, 14 Jul 2015 11:21:32 -0400 To: sam...@samkar.com<mailto:sam...@samkar.com> CC: webobjects-dev@lists.apple.com<mailto:webobjects-dev@lists.apple.com> I have to sort tens of thousands of rows. :( It looks like I have to resort to creating a view in the database. On Jul 14, 2015, at 11:18 AM, Samuel Pelletier <sam...@samkar.com<mailto:sam...@samkar.com>> wrote: I do not think so, the table reference may be required for more complex select with joins and the frameworks cannot really know when put it or not (or it would require a deep analyses of the query) Unless you have to sort thousands of rows and most of them will never be displayed or used, I would sort in memory. If you do not specify a fetch limit, you can probably sort in memory... Samuel Le 2015-07-14 à 10:46, Calven Eggert <cal...@mac.com<mailto:cal...@mac.com>> a écrit : Is there a way in WO to remove the t0 from the order by clause? On Jul 14, 2015, at 10:29 AM, Theodore Petrosky <tedp...@yahoo.com<mailto:tedp...@yahoo.com>> wrote: interesting this worked in postgresql: SELECT (EXTRACT(YEAR FROM c_materials_close_ns_timestamp) * 100 + EXTRACT(MONTH FROM c_materials_close_ns_timestamp)) AS t_test FROM t_grid t0 ORDER BY t_test ASC’ notice the ORDER BY t_test has no table listed if I put in t0 it fails i.e. t0.t_test SELECT (EXTRACT(YEAR FROM c_materials_close_ns_timestamp) * 100 + EXTRACT(MONTH FROM c_materials_close_ns_timestamp)) AS t_test FROM t_grid t0 ORDER BY t0.t_test ASC; LOG: statement: SELECT (EXTRACT(YEAR FROM c_materials_close_ns_timestamp) * 100 + EXTRACT(MONTH FROM c_materials_close_ns_timestamp)) AS t_test FROM t_grid t0 ORDER BY t0.t_test ASC; ERROR: column t0.t_test does not exist LINE 1: ...ns_timestamp)) AS t_test FROM t_grid t0 ORDER BY t0.t_test ... ^ but this did: colorgrid=# SELECT (EXTRACT(YEAR FROM c_materials_close_ns_timestamp) * 100 + EXTRACT(MONTH FROM c_materials_close_ns_timestamp)) AS t_test FROM t_grid t0 ORDER BY t_test ASC; LOG: statement: SELECT (EXTRACT(YEAR FROM c_materials_close_ns_timestamp) * 100 + EXTRACT(MONTH FROM c_materials_close_ns_timestamp)) AS t_test FROM t_grid t0 ORDER BY t_test ASC; t_test -------- 199906 199908 199908 199908 199909 199909 On Jul 14, 2015, at 9:59 AM, Calven Eggert <cal...@mac.com<mailto:cal...@mac.com>> wrote: As it turns out, this method does not work in the WO application. (It does work when executing it from a SQL command line) The fetch spec fails when the new column is included the sort: EvaluateExpression failed: <com.webobjects.jdbcadaptor.OraclePlugIn$OracleExpression: SELECT (EXTRACT(YEAR FROM APPT_DATE) * 100 + EXTRACT(MONTH FROM APPT_DATE)) AS "YEARMTH", FROM the_table t0 ORDER BY t0.YEARMTH ASC, Next exception:SQL State:42000 -- error code: 904 -- msg: ORA-00904: "T0"."YEARMTH": invalid identifier If the new column is removed from the sort then the select statement works: SELECT (EXTRACT(YEAR FROM APPT_DATE) * 100 + EXTRACT(MONTH FROM APPT_DATE)) AS “YEARMTH" FROM the_table t0 This is the new column created in EntityModeler: (EXTRACT(YEAR FROM APPT_DATE) * 100 + EXTRACT(MONTH FROM APPT_DATE)) AS “YEARMTH" Any other ideas, other than creating a new view in the database or new column in the table? Le 2015-07-13 à 16:48, Calven Eggert <cal...@mac.com<mailto:cal...@mac.com>> a écrit : Thanks Chuck! I had to add the ŒAS¹ clause to the Read Format, otherwise, I received an invalid SQL statement. I then took advantage of the situation by concatenating my two values (year and month) and padded the month so that I would always get a 6 digit string so I could then do some comparisons with the value for a report. Read Format: EXTRACT(YEAR FROM APPT_DATE) || LPAD(EXTRACT(MONTH FROM APPT_DATE), 2, '0') AS YM On Jul 13, 2015, at 4:10 PM, Chuck Hill <ch...@gevityinc.com<mailto:ch...@gevityinc.com>> wrote: Sorry, not derived. Battling too many tasks this morning. A read format should do it (so the same column defined as three class property attributes: theDate, theDateYear and theDateMonth (feel free to pick better names :-) <DE4C48C1-45C2-420F-A017-59C3BC72CBD6.png> On 2015-07-13, 1:05 PM, "Theodore Petrosky" wrote: I just looked in there. How do you set that up? I tried playing but I guess I don¹t understand the derived column. Thanks Chuck! On Jul 13, 2015, at 2:20 PM, Chuck Hill <ch...@gevityinc.com<mailto:ch...@gevityinc.com>> wrote: Just a note that you can use the extract (year from theDate) and extract (month from theDate) in a derived column in EntityModeler which avoids the need for a view. On 2015-07-13, 11:16 AM, "Theodore Petrosky" wrote: you didn¹t mention your database, but I have done things like this with a view. I created a view with the appropriate columns and in the select statement I put in the order by CREATE OR REPLACE VIEW myNewView AS SELECT info1, info2, theDate FROM theTable ORDER BY extract (year from theDate) desc, extract (month from theDate) asc; A one point I actually set up some rules to make the ŒVIEW¹ updatable. YMMV obviously depending on you access to the backend, and if Views are supported. Ted On Jul 13, 2015, at 12:02 PM, Calven Eggert <cal...@mac.com<mailto:cal...@mac.com>> wrote: Hi, All I have a fetch that sorts records by three columns where one of the columns is a date (timestamp). Is there a way to sort the date by month & year only? Thanks, Calven Here is the current fetch: ... EOSortOrdering sortBySource = new EOSortOrdering("source", EOSortOrdering.CompareCaseInsensitiveAscending); EOSortOrdering sortByAppt = new EOSortOrdering("appointmentDate", EOSortOrdering.CompareCaseInsensitiveAscending); EOSortOrdering sortBySite = new EOSortOrdering(³site", EOSortOrdering.CompareCaseInsensitiveAscending); NSMutableArray orderings = new NSMutableArray(); orderings.addObject(sortBySource); orderings.addObject(sortByAppt); orderings.addObject(sortBySite); EOFetchSpecification spec = new EOFetchSpecification(³records", qual, orderings); _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yaho o.com<http://o.com/> This email sent to tedp...@yahoo.com<mailto:tedp...@yahoo.com> _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevity inc.com<http://inc.com/> This email sent to ch...@gevityinc.com<mailto:ch...@gevityinc.com> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar. com This email sent to sam...@samkar.com<mailto:sam...@samkar.com> _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/lists%40thetimmy.c om This email sent to li...@thetimmy.com<mailto:li...@thetimmy.com> _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.c om This email sent to ch...@gevityinc.com<mailto:ch...@gevityinc.com> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.com This email sent to tedp...@yahoo.com<mailto:tedp...@yahoo.com> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com This email sent to sam...@samkar.com<mailto:sam...@samkar.com> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/racso_gp%40hotmail.com This email sent to racso...@hotmail.com<mailto:racso...@hotmail.com> _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com<mailto:Webobjects-dev@lists.apple.com>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.com This email sent to tedp...@yahoo.com<mailto:tedp...@yahoo.com>
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (Webobjects-dev@lists.apple.com) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to arch...@mail-archive.com
