Hi Calven, Yeah, that screen shot was just a quick example to show where the goes. The SQL extract value does not match a Timestamp data type. Thanks for catching that.
Chuck From: Calven Eggert <[email protected]<mailto:[email protected]>> Date: Tuesday, July 14, 2015 at 12:22 PM To: Chuck Hill <[email protected]<mailto:[email protected]>> Cc: Oscar González <[email protected]<mailto:[email protected]>>, Samuel Pelletier <[email protected]<mailto:[email protected]>>, "[email protected]<mailto:[email protected]>" <[email protected]<mailto:[email protected]>> Subject: Re: EOFetchSpecification - sorting by partial date Guys, Thanks so much for all you assistance and time spent on this issue. It now works!!! woohoo! Here is my final screenshot, only difference from Chuck’s being I stored the value in an Integer. Calven PS - User: “How hard could it be to sort that report by month and year?” :-) [cid:[email protected]] On Jul 14, 2015, at 1:46 PM, Chuck Hill <[email protected]<mailto:[email protected]>> 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: [email protected]<mailto:[email protected]> Date: Tue, 14 Jul 2015 11:21:32 -0400 To: [email protected]<mailto:[email protected]> CC: [email protected]<mailto:[email protected]> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 <[email protected]<mailto:[email protected]>> 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 ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yaho o.com<http://o.com/> This email sent to [email protected]<mailto:[email protected]> _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevity inc.com<http://inc.com/> This email sent to [email protected]<mailto:[email protected]> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar. com This email sent to [email protected]<mailto:[email protected]> _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/lists%40thetimmy.c om This email sent to [email protected]<mailto:[email protected]> _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevityinc.c om This email sent to [email protected]<mailto:[email protected]> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.com This email sent to [email protected]<mailto:[email protected]> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com This email sent to [email protected]<mailto:[email protected]> Calven _______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]<mailto:[email protected]>) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/racso_gp%40hotmail.com This email sent to [email protected]<mailto:[email protected]> Calven
_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list ([email protected]) Help/Unsubscribe/Update your Subscription: https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com This email sent to [email protected]
