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]> 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 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 >>>>>>>>>>> >>>>>>>>>>> <https://lists.apple.com/mailman/options/webobjects-dev/chill%40gevity> >>>>>>>>>>> inc.com <http://inc.com/> >>>>>>>>>>> >>>>>>>>>>> This email sent to [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] > 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]
