Menu
▾
▴
#1479 Firstof Function Week Constant
closed-fixed
9
2010-04-02
2008-08-28
No
Hi,
I found there is discrepancy between SQLJ function FirstOf and PostgreSql function firstof.
In SQLJ, "DY" is day of week
else if ("DY".equals(XX)) // Week
cal.set(Calendar.DAY_OF_WEEK, Calendar.SUNDAY);
but in Postgres, it's a day!
ELSEIF $2 IN ('DAY','DY','D') THEN
datepart = 'day';
This affects some reports that are using firstof function, i.e. Weekly Invoice
Best regards,
Armen Rizal
There are definitely differences in all three functions (oracle, postgresql and sqlj) - affecting mostly reports.
SQLJ:
support as second parameter
MM - first of month
DY - first of week
Q - first of quarter
defaults to drop hours from the date with any other value
Oracle:
just call trunc supporting many more
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/functions230.htm#i1002084
Postgresql: calls date_trunc trying to support the same oracle notation:
http://www.postgresql.org/docs/8.1/static/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC
Year (ISO Year translated to year)
Quarter
Month
Week (ISO Week translated to week)
Day
Hour
Minute
!!! Starting day of week (DAY/DY/D) is wrongly translated to 'day'
Steps to reproduce:
execute these two equivalent queries in oracle and postgres
oracle -> select firstof(date'2010-03-16', 'DY') from dual -> result 2010-03-14
postgresql -> select firstof(date'2010-03-16', 'DY') -> result 2010-03-16
How is it used on the different views on a seed of Adempiere:
MM
DD
DY -> rv_c_invoice_prodweek, rv_c_invoice_week
So, as Armen said, reports based on these two views are broken in postgresql.
Regards,
Carlos Ruiz
Committed revision 11677.
http://adempiere.svn.sourceforge.net/adempiere/?rev=11677&view=rev
Found that db function was different than ddlutils function and fixed that also.
Regards,
Carlos Ruiz
Hi Carlos,
Thank you for your help. I checked the revision and I think the scripts were swapped between postgresql and oracle. I will test it as soon as possible.
Rgds,
Armen
Thanks Armen by the quick peer review and notification. You're right.
Committed revision 11684.
http://adempiere.svn.sourceforge.net/adempiere/?rev=11684&view=rev
Regards,
Carlos Ruiz
Merged to trunk in Revision: 11694
http://adempiere.svn.sourceforge.net/adempiere/?rev=11694&view=rev
Tony
This Tracker item was closed automatically by the system. It was
previously set to a Pending status, and the original submitter
did not respond within 14 days (the time period specified by
the administrator of this Tracker).