SQL Status HY000 / Query is too complex

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER
Post Reply
lksdfjbv
Posts: 2
Joined: Sun May 16, 2010 10:59 am

SQL Status HY000 / Query is too complex

Post by lksdfjbv »

Dear all,

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.

Alex



***EDIT: When following Villeroy's advice of creating a new database from scratch and then simply drag the dbf file (http://user.services.openoffice.org/en/ ... =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 ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?)]
OOO 3.1.1
Linux Fedora 12
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: SQL Status HY000 / Query is too complex

Post by eremmel »

I think that your table is too large to copy to Base's internal HSQLDB. That is why copy action is failing.

You might also try to connect Base to an external database like MySQL or HSQLDB and then try the copy action (I hope this action is row oriented).

When you have a Windows system available you might try to use an ODBC connection to your database. It might be that through this ODBC connection you can perform more complex queries.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
lksdfjbv
Posts: 2
Joined: Sun May 16, 2010 10:59 am

Re: SQL Status HY000 / Query is too complex

Post by lksdfjbv »

Thanks eremmel. I finally gave up on Base for this. I was able to open the file on Access 2007, exported to a text file, and worked the format on Epsilon to make it readable by MySQL. Once in this format I used the LOAD DATA statement, and was able to aggregate the data as a charm. SQL rules.
OOO 3.1.1
Linux Fedora 12
Post Reply