回复: 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 jacopo.cappell...@hotwaxmedia.com编写： 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 jacopo.cappell...@hotwaxmedia.com编写：
回复: 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 jacopo.cappell...@hotwaxmedia.com编写： 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 jacopo.cappell...@hotwaxmedia.com编写：
回复: 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 jacopo.cappell...@hotwaxmedia.com编写：
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
jacopo.cappell...@hotwaxmedia.com 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 jacopo.cappell...@hotwaxmedia.com编写：
回复: 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 jacopo.cappell...@hotwaxmedia.com编写：
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:
alias entity-alias=EX name=trx_date function=month/
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:
alias name=month group-by=true
complex-alias operator=month()
complex-alias-field entity=Expense field=trx_date
/complex-alias
/alias
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智能手机
