To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=83154
Issue #|83154
Summary|Group By on fields using BETWEEN or IN or ... criteria
| are not interpreted well, causing the Group by to dis
|sapear on those fields
Component|Database access
Version|OOo 2.3
Platform|PC
URL|
OS/Version|Windows XP
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P3
Subcomponent|none
Assigned to|dbaneedsconfirm
Reported by|chojin
------- Additional comments from [EMAIL PROTECTED] Wed Oct 31 10:04:24 +0000
2007 -------
I'm using OpenOffice 2.3 (nl) Base with MySQL 5.0.24a-community-nt trough JDBC
Create a simple query like this, using design view:
- Field Date, Function: GROUP BY, Criteria: BETWEEN #11-20-2006# AND
#12-30-2006#
- Field Weight, Function: SUM
Resulting in this SQL-string:
SELECT `date`, SUM( `weight` ) FROM `openbase`.`bakken`
GROUP BY `date`
HAVING ( ( `date` BETWEEN {D '2006-11-20' } AND {D '2006-12-30' } ) )
Executing this query works correctly
But when the query is saved, closed and reopend in design mode, the GROUP BY
Function of the Date field is dissapeared.
The SQL string now looks like this:
SELECT `datum`, SUM( `gewicht` ) FROM `openbase`.`bakken`
WHERE ( ( `datum` BETWEEN {D '2006-11-20' } AND {D '2006-12-30' } ) )
And executing the query results in an error stating that using Grouped functions
like SUM without a Group By clause is illegal.
This also happens when switching from Design view to SQL view and back to Design
view. So I assume the problem is in the interpreting of the SQL..
This however doesn't happen when when another Criteria is added to the Date
field:
- Field Date, Function: GROUP BY, Criteria: BETWEEN #11-20-2006# AND
#12-30-2006#, or: #01-01-1997#
- Field Weight, Function: SUM
results in:
SELECT `date`, SUM( `weight` ) FROM `openbase`.`bakken`
GROUP BY `date`
HAVING ( ( `date` BETWEEN {D '2006-11-20' } AND {D '2006-12-30' } ) OR ( `date`
= {D '1997-01-01' } ) )
which gets interpreted correctly and the Group By function is kept on the Date
field when the query is closed and reopened in design-view or when switching
between SQL view and Design view.
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
