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.
[cid:241C0C61-3586-4AD1-AFE9-C3D2875C2F33]
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
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
inc.com<http://inc.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
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
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
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
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
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
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]
