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?” :-) > On Jul 14, 2015, at 1:46 PM, Chuck Hill <[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 > <https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yaho> > o.com <http://o.com/> > This email sent [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 > <https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevity> > inc.com <http://inc.com/> > > This email sent [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 > <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 > <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 > <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 > <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 > <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 > <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]
