回复: Re: GROUP BY month(date)
Jacopo, I tested the patches again on the ofbiz instance configured with postgresQL database, the patches worked pretty well. Although I did not have the chance to test them under oracle or MySql, I guess the patches should work fine for these two DBMS since they both also support SQL grammar EXTRACT(YEAR/MONTH/DAY FROM ...). Anyway, that's what I got till now. Hope it helps. -- 发自我的ONEPLUS智能手机 Jacopo Cappellato 编写： >Thank you! >It would be even better if you could test the latest patch I have attached to >Jira: > >https://issues.apache.org/jira/secure/attachment/12683398/OFBIZ-5146.patch > >This last one also supports the "day" function. > >Regards, > >Jacopo > >On Nov 25, 2014, at 4:56 AM, wulei.bj...@gmail.com wrote: > >> Jacopo, >> >> Sure, I'll test this new patch and let you know the results once it's done. >> >> -- >> 发自我的ONEPLUS智能手机 >> >> >> >> Jacopo Cappellato 编写： >> >
回复: Re: GROUP BY month(date)
Jacopo, I tested both patches and it turns out at least Derby is not happy with these changes. I tried SQL grammar EXTRACT(YEAR/MONTH/DAY FROM '2014-11-26') literally in database Derby and I found Derby does not support such grammar. I then searched WWW and found MySQL and Oracle support such grammar well. However I didn't get the chance to test the patches in these two databases. Whereas I did configured an Ofbiz instance with database postgresQL in my lab environment, I'll try the SQL grammar in postgresQL tomorrow, and if it supports the grammar well, I'll test the patches again in that instance. I will let you know as soon as results come out. -- 发自我的ONEPLUS智能手机 Jacopo Cappellato 编写： >Thank you! >It would be even better if you could test the latest patch I have attached to >Jira: > >https://issues.apache.org/jira/secure/attachment/12683398/OFBIZ-5146.patch > >This last one also supports the "day" function. > >Regards, > >Jacopo > >On Nov 25, 2014, at 4:56 AM, wulei.bj...@gmail.com wrote: > >> Jacopo, >> >> Sure, I'll test this new patch and let you know the results once it's done. >> >> -- >> 发自我的ONEPLUS智能手机 >> >> >> >> Jacopo Cappellato 编写： >> >
回复: Re: GROUP BY month(date)
Jacopo,
Sure, I'll test this new patch and let you know the results once it's done.
--
发自我的ONEPLUS智能手机
Jacopo Cappellato 编写：
>It would be great if you could test this improved version of the patch that I
>sent you: it internally uses the EXTRACT function that should be a standard
>function since SQL-99.
>
>Thanks,
>
>Jacopo
>
>
>Index: framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>===
>--- framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>(revision 1641394)
>+++ framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>(working copy)
>@@ -65,6 +65,8 @@
> functionPrefixMap.put("count-distinct", "COUNT(DISTINCT ");
> functionPrefixMap.put("upper", "UPPER(");
> functionPrefixMap.put("lower", "LOWER(");
>+functionPrefixMap.put("year", "EXTRACT(YEAR FROM ");
>+functionPrefixMap.put("month", "EXTRACT(MONTH FROM ");
> }
>
> /** Contains member-entity alias name definitions: key is alias, value is
> ModelMemberEntity */
>@@ -480,7 +482,7 @@
> fieldSet = alias.getFieldSet();
> }
> }
>-if ("count".equals(alias.function) ||
>"count-distinct".equals(alias.function)) {
>+if ("count".equals(alias.function) ||
>"count-distinct".equals(alias.function) || "year".equals(alias.function) ||
>"month".equals(alias.function)) {
> // if we have a "count" function we have to change the type
> type = "numeric";
> }
>
>
>On Nov 18, 2014, at 11:40 AM, Jacopo Cappellato
> wrote:
>
>> Thanks for letting me know it worked for you.
>> I will commit it sometime soon if no one will object.
>>
>> Best regards,
>>
>> Jacopo
>>
>> On Nov 18, 2014, at 4:37 AM, wulei.bj...@gmail.com wrote:
>>
>>> Dear Jacopo,
>>>
>>> Thanks so much for your kind help, the solution works perfectly as it is
>>> exactly what I wanted ! Now I can summary the total in the dimensions I
>>> want, i.e. MONTH and YEAR. Thanks so much again, and I also think it is
>>> helpful to commit these two added functions to public trunk in case others
>>> have similar requirements like mine.
>>>
>>> --
>>> 发自我的ONEPLUS智能手机
>>>
>>>
>>>
>>> Jacopo Cappellato 编写：
>>>
>>
>
回复: Re: GROUP BY month(date)
Dear Jacopo,
Thanks so much for your kind help, the solution works perfectly as it is
exactly what I wanted ! Now I can summary the total in the dimensions I want,
i.e. MONTH and YEAR. Thanks so much again, and I also think it is helpful to
commit these two added functions to public trunk in case others have similar
requirements like mine.
--
发自我的ONEPLUS智能手机
Jacopo Cappellato 编写：
>month is not a standard function of SQL even if nowadays is available in
>most database management systems.
>However you may find it useful the patch below that adds the support for
>the date and month functions (if others are interested it could be
>committed to public repository).
>In order to use it you will have to define an alias field like:
>
>
>
>I hope it helps,
>
>Jacopo
>
>Index:
>../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>
>===
>
>---
>../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>(revision
>1637645)
>
>+++
>../trunk/framework/entity/src/org/ofbiz/entity/model/ModelViewEntity.java
>(working
>copy)
>
>@@ -65,6 +65,8 @@
>
> functionPrefixMap.put("count-distinct", "COUNT(DISTINCT ");
>
> functionPrefixMap.put("upper", "UPPER(");
>
> functionPrefixMap.put("lower", "LOWER(");
>
>+functionPrefixMap.put("year", "YEAR(");
>
>+functionPrefixMap.put("month", "MONTH(");
>
> }
>
>
>
> /** Contains member-entity alias name definitions: key is alias, value
>is ModelMemberEntity */
>
>@@ -480,7 +482,7 @@
>
> fieldSet = alias.getFieldSet();
>
> }
>
> }
>
>-if ("count".equals(alias.function) ||
>"count-distinct".equals(alias.function)) {
>
>+if ("count".equals(alias.function) ||
>"count-distinct".equals(alias.function) || "year".equals(alias.function) ||
>"month".equals(alias.function)) {
>
> // if we have a "count" function we have to change the type
>
> type = "numeric";
>
> }
>
>
>On Sat, Nov 15, 2014 at 8:19 AM, wulei.bj...@gmail.com <
>wulei.bj...@gmail.com> wrote:
>
>> Guys,
>>
>> I have an Expense entity with a trx_date colum of type DATE to record the
>> date that I bought sth.
>> And then I wanna summary the total expenses by month, and I hit a blocker.
>> I tried to create an view entity with a complex alias to represent the
>> month, it's like:
>>
>>
>>
>>
>>
>>
>> However, it did not work. Could anyone help me out here to get what I
>> wanted? Thanks so much!
>>
>> I am using the default database derby by the way. And Ofbiz version is
>> 12.04.
>> --
>> 发自我的ONEPLUS智能手机
