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