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])
>>>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>>>
>>>>>>> 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])
>>>>> Help/Unsubscribe/Update your Subscription:
>>>>>
>>>>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.
>>>>> 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/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])
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
