• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: EOFetchSpecification - sorting by partial date
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: EOFetchSpecification - sorting by partial date


  • Subject: Re: EOFetchSpecification - sorting by partial date
  • From: Chuck Hill <email@hidden>
  • Date: Tue, 14 Jul 2015 19:58:47 +0000
  • Thread-topic: EOFetchSpecification - sorting by partial date

The direction would be part of the EOSortOrdering for that property.

From: Theodore Petrosky <email@hidden>
Date: Tuesday, July 14, 2015 at 12:20 PM
To: Chuck Hill <email@hidden>
Cc: Oscar González <email@hidden>, WebObjects-Dev <email@hidden>
Subject: Re: EOFetchSpecification - sorting by partial date

wow this is great! and I even understand the ‘Derived’ selection now. I still don’t see how to set the sort direction. Oscar’s example of the SQL it generates shows an order by of ASC. what if I want DESC?

enquiring minds need this stuff!!!



On Jul 14, 2015, at 1:46 PM, Chuck Hill <email@hidden> wrote:

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.


<Entity_Modeler_-_CadreCore_CadreCore_eomodelgroup_-_Eclipse_SDK_-__Users_chuck_Documents_workspace[1].png>

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@hidden
Date: Tue, 14 Jul 2015 11:21:32 -0400
To: email@hidden
CC: email@hidden

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@hidden> 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@hidden> 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@hidden> 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@hidden> 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@hidden> 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@hidden> 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@hidden> 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@hidden> 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@hidden)
Help/Unsubscribe/Update your Subscription:

o.com
This email sent to email@hidden


_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

inc.com

This email sent to email@hidden



Calven



_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

com

This email sent to email@hidden

_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

om

This email sent to email@hidden


_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:
om

This email sent to email@hidden


Calven



_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

This email sent to email@hidden



Calven



_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

This email sent to email@hidden



Calven




_______________________________________________ Do not post admin requests to the list. They will be ignored. Webobjects-dev mailing list (email@hidden) Help/Unsubscribe/Update your email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

This email sent to email@hidden

 _______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list      (email@hidden)
Help/Unsubscribe/Update your Subscription:

This email sent to email@hidden

References: 
 >EOFetchSpecification - sorting by partial date (From: Calven Eggert <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Theodore Petrosky <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Chuck Hill <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Theodore Petrosky <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Calven Eggert <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Samuel Pelletier <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Calven Eggert <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Timothy Worman <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Chuck Hill <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Calven Eggert <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Theodore Petrosky <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Calven Eggert <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Samuel Pelletier <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Calven Eggert <email@hidden>)
 >RE: EOFetchSpecification - sorting by partial date (From: Oscar González <email@hidden>)
 >Re: EOFetchSpecification - sorting by partial date (From: Theodore Petrosky <email@hidden>)

  • Prev by Date: Re: EOFetchSpecification - sorting by partial date
  • Next by Date: WOWODC16 ??
  • Previous by thread: Re: EOFetchSpecification - sorting by partial date
  • Next by thread: WOWODC16 ??
  • Index(es):
    • Date
    • Thread