I'm posting after some work and search, and I apologize in advance if I have missed something very obvious in the forum or on the web, but I haven't been able to find anything that helps me solve my problem.
I am trying to reduce a pretty large dbf file (10 columns by 9 1/2 million rows) with Base, aggregating (averaging) by one of its columns.
The data consists of student scores in a national math test, and the columns indicate school ID, test score, state in which it was conducted, grade of student, etc. for each individual student. I'm trying to aggregate the data by school, and create a new variable with the average score for each school.
I open Base and connect to my existing database in dBase (.dbf) format. I am brand new to the SQL world and so I must be doing something very basic wrong because I cannot do simple queries like aggregating. When I state:
SELECT "SchoolID", AVG( "MathScore" ) AS "MathAvg" FROM "my.table" GROUP BY "SchoolID"
then Base displays two errors:
Error 1: The data content could not be loaded.
Error 2: SQL Status: HY000. The query can not be executed. It is too complex. Only "COUNT(*)" is supported.
In Villeroy's tutorial 'Calculations in Databases' i can read that "File based databases (dBase, spreadsheets, csv) support horizontal calculation only". I think this is the root of my problem but I haven't been able to figure out how to convert file based databases into true databases.
If I make a search for SQL Status: HY000, I get pointed to problems about primary keys. So, I open up my.table in edit mode, select the field with non-recurring values (StudentID) as a primary key, hit save, and after a while when the computer is done thinking, I open my table by double clicking on it and all the entries are gone! Plus, when I try to set a column as a primary key, it won't allow me to select field type as integer.
From reading threads and tutorials online I gather that simply connecting to an existing dbf base does not create a 'true' hsql database, and so I'm trying to understand how to do it. I thought that creating a table from the dbf file, and adding a primary key would solve the problem, but now I'm stuck with not even entries in my new table.
Sorry for the lengthy post, and thanks in advance for any help. I'm using OOo 3.1.1 on Linux Fedora 12, on a 64 bit Gateway.
***EDIT: When following Villeroy's advice of creating a new database from scratch and then simply drag the dbf file (viewtopic.php?f=40&t=5079
), I get the following error:
SQL Status: S1000
Error code: 40
S1000 General error java.lang.NullPointerException in statement [INSERT INTO "ENLACE2006(copy)" ( "field1",...,field10") VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?)]