OpenOffice.org Base – Help!

UNION – Error Message “The given command is not a SELECT statement. Only queries are allowed”

Union only works when “Run SQL term directly” is selected

Example:

SELECT “ID”, “GraceDate”, “Latitude”, – 179.5 “Longitude”, “-179.5” “anomaly_cm”
FROM “Grace2002”
UNION
SELECT “ID”, “GraceDate”, “Latitude”, – 178.5 “Longitude”, “-178.5” “anomaly_cm”
FROM “Grace2002”

I want to create a query that looks at both of these columns and returns all results in 1 column in numerical order.

Welcome to OOo Base and its challenges.

This is possible with a SQL UNION query, but the syntax tends to depend on the Database Engine that you are using. In any event the OOo Base GUI does not recognise UNION queries, but it is still possible to create the Query and run it DIRECTLY.

If you are using the embedded HSQL database, select “Create Query in SQL View…” from the queries window in Base. Type in the SQL commands listed below changing the field and table names as required, but before pressing f5 to run the SQL you must select the “Run SQL command directly” button in the toolbar [the icon has the letters SQL with a green check mark]. If this icon is not selected with will probably get an error. Hopefully if all goes well you can save the Query and it will always run the SQL command directly when double clicked in the Base window. If you are using another database engine the SQL syntax may differ. Let us know how it goes.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s