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]> 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
>>>>>>>> This email sent to [email protected]
>>>>>>>>
>>>>>>>>
>>>>>>>> _______________________________________________
>>>>>>>> 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/chill%40gevity
>>>>>>>> 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]
>>>
>>>
>>> _______________________________________________
>>> 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]>
_______________________________________________
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]
