Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

Hi Calven,

Yeah, that screen shot was just a quick example to show where the goes.   The 
SQL extract value does not match a Timestamp data type.  Thanks for catching 
that.

Chuck

From: Calven Eggert mailto:cal...@mac.com>>
Date: Tuesday, July 14, 2015 at 12:22 PM
To: Chuck Hill mailto:ch...@gevityinc.com>>
Cc: Oscar González mailto:racso...@hotmail.com>>, Samuel 
Pelletier mailto:sam...@samkar.com>>, 
"webobjects-dev@lists.apple.com<mailto:webobjects-dev@lists.apple.com>" 
mailto:webobjects-dev@lists.apple.com>>
Subject: Re: EOFetchSpecification - sorting by partial date

Guys,  Thanks so much for all you assistance and time spent on this issue. It 
now works!!! woohoo!  Here is my final screenshot, only difference from Chuck’s 
being I stored the value in an Integer.

Calven

PS - User: “How hard could it be to sort that report by month and year?”  :-)

[cid:76050C38-5015-4E96-898C-ACBFB1099E82@uhnres.utoronto.ca]

On Jul 14, 2015, at 1:46 PM, Chuck Hill 
mailto:ch...@gevityinc.com>> 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.




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: cal...@mac.com<mailto:cal...@mac.com>
Date: Tue, 14 Jul 2015 11:21:32 -0400
To: sam...@samkar.com<mailto:sam...@samkar.com>
CC: webobjects-dev@lists.apple.com<mailto:webobjects-dev@lists.apple.com>

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 
mailto:sam...@samkar.com>> 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 mailto:cal...@mac.com>> 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 
mailto: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 
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: 
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 Hil

Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

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

From: Theodore Petrosky mailto:tedp...@yahoo.com>>
Date: Tuesday, July 14, 2015 at 12:20 PM
To: Chuck Hill mailto:ch...@gevityinc.com>>
Cc: Oscar González mailto:racso...@hotmail.com>>, 
WebObjects-Dev 
mailto:webobjects-dev@lists.apple.com>>
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 
mailto:ch...@gevityinc.com>> 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.




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: cal...@mac.com<mailto:cal...@mac.com>
Date: Tue, 14 Jul 2015 11:21:32 -0400
To: sam...@samkar.com<mailto:sam...@samkar.com>
CC: webobjects-dev@lists.apple.com<mailto:webobjects-dev@lists.apple.com>

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 
mailto:sam...@samkar.com>> 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 mailto:cal...@mac.com>> 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 
mailto: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 
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: 
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 
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 :-)





On 2015-07-13, 1:05 PM, "Theodore Petrosky" 

Re: EOFetchSpecification - sorting by partial date

[Alexander Spohr]

Without having read this thread, I would assume you’ll need
EOSortOrdering

with your EOFetchSpecification.


> Am 14.07.2015 um 21:20 schrieb Theodore Petrosky :
> 
> 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!!!
> 

 ___
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

Re: EOFetchSpecification - sorting by partial date

[Calven Eggert]

Guys,  Thanks so much for all you assistance and time spent on this issue. It 
now works!!! woohoo!  Here is my final screenshot, only difference from Chuck’s 
being I stored the value in an Integer.

Calven

PS - User: “How hard could it be to sort that report by month and year?”  :-)



> On Jul 14, 2015, at 1:46 PM, Chuck Hill  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.
> 
> 
> 
> 
> 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: cal...@mac.com <mailto:cal...@mac.com>
> Date: Tue, 14 Jul 2015 11:21:32 -0400
> To: sam...@samkar.com <mailto:sam...@samkar.com>
> CC: webobjects-dev@lists.apple.com <mailto:webobjects-dev@lists.apple.com>
> 
> 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  <mailto:sam...@samkar.com>> 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 mailto:cal...@mac.com>> 
> 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  <mailto: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  <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: 
>  (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 

Re: EOFetchSpecification - sorting by partial date

[Theodore Petrosky]

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  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.
> 
> 
> 
> 
> 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: cal...@mac.com <mailto:cal...@mac.com>
> Date: Tue, 14 Jul 2015 11:21:32 -0400
> To: sam...@samkar.com <mailto:sam...@samkar.com>
> CC: webobjects-dev@lists.apple.com <mailto:webobjects-dev@lists.apple.com>
> 
> 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  <mailto:sam...@samkar.com>> 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 mailto:cal...@mac.com>> 
> 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  <mailto: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  <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: 
>  (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 th

Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

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: cal...@mac.com<mailto:cal...@mac.com>
Date: Tue, 14 Jul 2015 11:21:32 -0400
To: sam...@samkar.com<mailto:sam...@samkar.com>
CC: webobjects-dev@lists.apple.com<mailto:webobjects-dev@lists.apple.com>

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 
mailto:sam...@samkar.com>> 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 mailto:cal...@mac.com>> 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 
mailto: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 
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: 
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 
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 :-)





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 
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, t

RE: EOFetchSpecification - sorting by partial date

[Oscar González]

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: cal...@mac.com
Date: Tue, 14 Jul 2015 11:21:32 -0400
To: sam...@samkar.com
CC: webobjects-dev@lists.apple.com

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  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  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  
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  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: 
 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  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 :-)





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  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  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 sort

Re: EOFetchSpecification - sorting by partial date

[Calven Eggert]

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  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 > > 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 >> > 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 >>> > 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: 
 >>> (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  > 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 > > 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 :-)
>> 
>> 
>> 
>> 
>> 
>> 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 >> > wrote:
>>> 
>>> Just a note that you can use the  extract (year from theDate) and
>>> extract (month from theDate) in a derived column in Ent

Re: EOFetchSpecification - sorting by partial date

[Samuel Pelletier]

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  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 > > 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 >> > 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: 
>>> >> (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 >>> > 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  > 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 :-)
> 
> 
> 
> 
> 
> 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 > > 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
>>

Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

Interesting.   This is probably something that could be fixed in the database 
plugin.

Chuck

From: Calven Eggert mailto:cal...@mac.com>>
Date: Tuesday, July 14, 2015 at 7:46 AM
To: Theodore Petrosky mailto:tedp...@yahoo.com>>
Cc: Chuck Hill mailto:ch...@gevityinc.com>>, 
WebObjects-Dev 
mailto:webobjects-dev@lists.apple.com>>
Subject: Re: EOFetchSpecification - sorting by partial date

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 
mailto: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 
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: 
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 
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 :-)





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 
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 
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

Re: EOFetchSpecification - sorting by partial date

[Calven Eggert]

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  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 > > 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: 
>> > (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 >> > 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 >>> > 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 :-)
 
 
 
 
 
 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  > 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  > wrote:
> Hi, A

Re: EOFetchSpecification - sorting by partial date

[Theodore Petrosky]

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  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: 
>  (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 > > 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 >> > 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 :-)
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 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 >>> > 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 >>> > 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(

Re: EOFetchSpecification - sorting by partial date

[Calven Eggert]

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: 
 Le 2015-07-13 à 16:48, Calven Eggert  > 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 > > 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 :-)
>> 
>> 
>> 
>> 
>> 
>> 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 >> > 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 >> > 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)
>>> Help/Unsubscribe/Update your Subscription:
>>> 
>>> https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yaho
>>> 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)
> Help/Unsubscribe/Update your Subscription:
> 
> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.
> com
> 
> This email sent to sam...@samkar.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 Subs

Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

Yes, you can do it that way too.  The effect is the same as using the Read
Format - the calue can be temporarily out of sync if you change the date
value. The advantage of doing it in EntityModeler is that you can put this
into prototypes and have an application that supports multiple databases.
Done as a trigger, you need to re-implement to compensate for differences
between databases.

Chuck


On 2015-07-13, 6:54 PM,
"webobjects-dev-bounces+chill=gevityinc@lists.apple.com on behalf of
Timothy Worman" 
 wrote:

>Just asking out loud mostly out of curiosity and a desire to get abused -
>I haven¹t done this. Depending on the database could you use a trigger to
>update a ³-mm" column in the database on commit? Then model that
>column in addition to the date?
>
>If you weren¹t commonly committing changes to the initial date it seems
>like it would be pretty safe?
>
>Tim Worman
>UCLA GSE&IS
>
>
>> On Jul 13, 2015, at 2:18 PM, Calven Eggert  wrote:
>> 
>> I'm not going good to be changing the value of the date but thanks for
>>the heads up. Good idea on using the calculation to get a number!
>> 
>> Calven
>> 
>> 
>> On Jul 13, 2015, at 5:11 PM, Samuel Pelletier  wrote:
>> 
>>> Keep in mind that the YM value on your EO will not be updated if you
>>>change the apptDate unless you refresh the object from the database.
>>> 
>>> To save string creation and manipulation on the server, you may
>>>generate a number instead of a string...
>>> 
>>> EXTRACT(YEAR FROM APPT_DATE) * 100 + EXTRACT(MONTH FROM APPT_DATE) AS
>>>YM
>>> 
>>> Samuel
>>> 
 Le 2015-07-13 à 16:48, Calven Eggert  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  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 :-)
> 
> 
> 
> 
> 
> 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  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  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)
>> Help/Unsubscribe/Update your Subscription:
>> 
>>https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yaho
>>o.com
>> This email sent to tedp...@yahoo.c

Re: EOFetchSpecification - sorting by partial date

[Timothy Worman]

Just asking out loud mostly out of curiosity and a desire to get abused - I 
haven’t done this. Depending on the database could you use a trigger to update 
a “-mm" column in the database on commit? Then model that column in 
addition to the date?

If you weren’t commonly committing changes to the initial date it seems like it 
would be pretty safe?

Tim Worman
UCLA GSE&IS


> On Jul 13, 2015, at 2:18 PM, Calven Eggert  wrote:
> 
> I'm not going good to be changing the value of the date but thanks for the 
> heads up. Good idea on using the calculation to get a number!
> 
> Calven
> 
> 
> On Jul 13, 2015, at 5:11 PM, Samuel Pelletier  wrote:
> 
>> Keep in mind that the YM value on your EO will not be updated if you change 
>> the apptDate unless you refresh the object from the database.
>> 
>> To save string creation and manipulation on the server, you may generate a 
>> number instead of a string...
>> 
>> EXTRACT(YEAR FROM APPT_DATE) * 100 + EXTRACT(MONTH FROM APPT_DATE) AS YM
>> 
>> Samuel
>> 
>>> Le 2015-07-13 à 16:48, Calven Eggert  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  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 :-)
 
 
 
 
 
 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  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  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)
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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%40gevityinc.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@

Re: EOFetchSpecification - sorting by partial date

[Calven Eggert]

I'm not going good to be changing the value of the date but thanks for the 
heads up. Good idea on using the calculation to get a number!

Calven


> On Jul 13, 2015, at 5:11 PM, Samuel Pelletier  wrote:
> 
> Keep in mind that the YM value on your EO will not be updated if you change 
> the apptDate unless you refresh the object from the database.
> 
> To save string creation and manipulation on the server, you may generate a 
> number instead of a string...
> 
> EXTRACT(YEAR FROM APPT_DATE) * 100 + EXTRACT(MONTH FROM APPT_DATE) AS YM
> 
> Samuel
> 
>> Le 2015-07-13 à 16:48, Calven Eggert  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  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 :-)
>>> 
>>> 
>>> 
>>> 
>>> 
>>> 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  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  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)
 Help/Unsubscribe/Update your Subscription:
 https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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%40gevityinc.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)
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
>> 
>> This email sent to sam...@samkar.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/archive%40mail-archive.com

This email sent to arch...@mail-archive.com

Re: EOFetchSpecification - sorting by partial date

[Samuel Pelletier]

Keep in mind that the YM value on your EO will not be updated if you change the 
apptDate unless you refresh the object from the database.

To save string creation and manipulation on the server, you may generate a 
number instead of a string...

EXTRACT(YEAR FROM APPT_DATE) * 100 + EXTRACT(MONTH FROM APPT_DATE) AS YM

Samuel

> Le 2015-07-13 à 16:48, Calven Eggert  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 > > 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 :-)
>> 
>> 
>> 
>> 
>> 
>> 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 >> > 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 >> > 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 
>>> )
>>> Help/Unsubscribe/Update your Subscription:
>>> https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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%40gevityinc.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)
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
> 
> This email sent to sam...@samkar.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/archive%40mail-archive.com

This email sent

Re: EOFetchSpecification - sorting by partial date

[Calven Eggert]

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  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 :-)
> 
> 
> 
> 
> 
> 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 > > 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 > > 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 
>> )
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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%40gevityinc.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)
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

Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

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 :-)
[cid:DE4C48C1-45C2-420F-A017-59C3BC72CBD6]




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 
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 
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)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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%40gevityinc.com

This email sent to ch...@gevityinc.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/archive%40mail-archive.com

This email sent to arch...@mail-archive.com

Re: EOFetchSpecification - sorting by partial date

[Theodore Petrosky]

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  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  > 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 
> )
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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%40gevityinc.com 
> 
> 
> This email sent to ch...@gevityinc.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/archive%40mail-archive.com

This email sent to arch...@mail-archive.com

Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

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 
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)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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%40gevityinc.com

This email sent to ch...@gevityinc.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/archive%40mail-archive.com

This email sent to arch...@mail-archive.com

Re: EOFetchSpecification - sorting by partial date

[Theodore Petrosky]

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  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)
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/tedpet5%40yahoo.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/archive%40mail-archive.com

This email sent to arch...@mail-archive.com

Re: EOFetchSpecification - sorting by partial date

[Chuck Hill]

You could create two read only, columns with a definition that extracts the 
year and month using SQL functions and use those columns to sort on.

Chuck

On 2015-07-13, 9:36 AM, "Calven Eggert" wrote:

Thanks for the idea.  sorting in memory is the other option I've considered, 
however, was hoping to get the results faster at the database end.

On Jul 13, 2015, at 12:27 PM, Samuel Pelletier 
mailto:sam...@samkar.com>> wrote:

Calven,

There is no easy way on the database but you can add a method that returns a 
formatted string or int and sort in memory on it.

To use an int, you take the date year*100 + month number, for a string a 
formatter like this "-MM".

For more readable code, add an ERXKey on your entity class like this:
public static ERXKey YEAR_MONTH = new ERXKey("yearMonth");

and sort like this:
sortedRows = EntityClass.YEAR_MONTH.ascs().sorted(rowsFromDB);

BTW, if you are using the Wonder templates, your sort ordering can be created 
like this with no strings constants:

orderings = 
SOURCE.ascInsensitive().then(APPOINTMENT_DATE.ascInsensitive()).then(SITE.ascInsensitive());

Samuel


Le 2015-07-13 à 12:02, Calven Eggert mailto:cal...@mac.com>> a 
écrit :

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)
Help/Unsubscribe/Update your Subscription:
https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com

This email sent to sam...@samkar.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

Re: EOFetchSpecification - sorting by partial date

[Calven Eggert]

Thanks for the idea.  sorting in memory is the other option I’ve considered, 
however, was hoping to get the results faster at the database end.

> On Jul 13, 2015, at 12:27 PM, Samuel Pelletier  wrote:
> 
> Calven,
> 
> There is no easy way on the database but you can add a method that returns a 
> formatted string or int and sort in memory on it.
> 
> To use an int, you take the date year*100 + month number, for a string a 
> formatter like this "-MM".
> 
> For more readable code, add an ERXKey on your entity class like this:
> public static ERXKey YEAR_MONTH = new ERXKey("yearMonth");
> 
> and sort like this:
> sortedRows = EntityClass.YEAR_MONTH.ascs().sorted(rowsFromDB);
> 
> BTW, if you are using the Wonder templates, your sort ordering can be created 
> like this with no strings constants:
> 
> orderings = 
> SOURCE.ascInsensitive().then(APPOINTMENT_DATE.ascInsensitive()).then(SITE.ascInsensitive());
> 
> Samuel
> 
> 
>> Le 2015-07-13 à 12:02, Calven Eggert  a écrit :
>> 
>> 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)
>> Help/Unsubscribe/Update your Subscription:
>> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
>> 
>> This email sent to sam...@samkar.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

Re: EOFetchSpecification - sorting by partial date

[Samuel Pelletier]

Calven,

There is no easy way on the database but you can add a method that returns a 
formatted string or int and sort in memory on it.

To use an int, you take the date year*100 + month number, for a string a 
formatter like this "-MM".

For more readable code, add an ERXKey on your entity class like this:
public static ERXKey YEAR_MONTH = new ERXKey("yearMonth");

and sort like this:
sortedRows = EntityClass.YEAR_MONTH.ascs().sorted(rowsFromDB);

BTW, if you are using the Wonder templates, your sort ordering can be created 
like this with no strings constants:

orderings = 
SOURCE.ascInsensitive().then(APPOINTMENT_DATE.ascInsensitive()).then(SITE.ascInsensitive());

Samuel


> Le 2015-07-13 à 12:02, Calven Eggert  a écrit :
> 
> 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)
> Help/Unsubscribe/Update your Subscription:
> https://lists.apple.com/mailman/options/webobjects-dev/samuel%40samkar.com
> 
> This email sent to sam...@samkar.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/archive%40mail-archive.com

This email sent to arch...@mail-archive.com