Apache OpenOffice Community Forum

Posted: **Wed Dec 26, 2007 1:05 pm**

Hello,

I am a oracle database developer. Today I first tried "OpenOffice Base". I have a ods-spreadsheet with a list of persons (first name, last name , address, telephon number, email,...). I created a Base Database with my spreadsheet as existing database. Then I opened the SQL-Editor and tried following statement:

Code: Select all

select email, count(*) as quantity
from person
group by email
having count(*) > 1

But this doesn't work. The second column "quantity" is always empty. I would like to find all duplicate email. With "Oracle-SQL" I would write the query as I show before. Is there a possibility to find out all duplicate email in "OpenOffice Base"

For me it seems that "group by" doesn't work in "OpenOffice Base". The query

Code: Select all

select last_name
from person
group by last_name

have the same result as

Code: Select all

select last_name
from person

I tried all these queries with Open Office Base 2.2 and 2.3

~Bernd

Posted: **Tue Jan 01, 2008 5:44 am**

I think that you will find that the issue is due to limitations imposed when you create a database by linking to an existing spreadsheet rather than importing the data into Base. The database engine support for linked spreadsheets is rather primitive. If you open the database in Calc and select its contents (including the header row), you can create a new blank database in Base and paste this selection in as a table. You will find that your standard query syntax works on a native table in base.

Alternatively if you want to stay with the spreadsheet paradigm then you can do the same with a data pilot and a filter of the pilot output table.

Apache OpenOffice Community Forum

select with "group by" and "having"

select with "group by" and "having"

Re: select with "group by" and "having"