Computer Tips From A Computer Guy

Posted: 14 Mar 2011 04:22 AM PDT

For even more control over your queries, use the SQL editor in OpenOffice Base. Instead of a guided wizard or grid template, you enter your query exactly as you want it.

From creating new database elements to creating highly customized results for reports, the Structured Query Language has the commands you need to make it happen. Contrary to popular belief, SQL queries can be created in OpenOffice Base just as easily as in Microsoft Access.

SQL Editor

Open an OpenOffice Base database. If you have not already created one, check out this tutorial for creating a database. Select Queries in the Database pane on the left side of the database window. In the Tasks pane near the top of the database window, press Create Query in SQL View.

Instead of any type of template or wizard, all you will see is a blank SQL editor window.

Enter your SQL query in the editor window. If you are unfamiliar with how to use SQL commands and what syntax to use, look through a few of the wonderful tutorials at W3Schools.

The SQL Basic section covers most of the commands you would use regularly. To help with editing and organizing, place each command on a separate line. Place a semi-colon ( ; ) at the end of the last line. Your SQL query will not run without this syntax.

The query above selects the Name field from the Customers table, but only where the Age field is less than 20. This is a very basic query, but displays how OpenOffice Base's SQL Editor distinguishes between commands and field names. Commands are listed in blue, while your table and field names are list in green. All other text, such as conditional criteria, is displayed in black.

You can test your SQL query from within the SQL Editor and view your results before actually saving your query. Press the Run Query button, which looks like two small pieces of paper with a green check mark in the front.

Your query results are displayed in the top pane and your query is displayed in the bottom pane. Please note that when you run the query, OpenOffice Base automatically reformats it to a single line.

If the results are what you expect, save your SQL query. Press the Save button or go to File and select Save As. Enter a name for your query and press OK.

This query will now display in the list of available queries in your database window. Some portions of the query can be edited using the query template window.

Some commands are not recognized until you open the query in SQL view. If you try to edit the query, it automatically opens in Design View. Go to View in the Design window and choose Switch Design View On/Off to open the SQL Editor.

Tips and Advice

Always name your SQL queries something distinct and descriptive. For instance, I named the above query Under 20 since it looked for customers under the age of 20. As your list of queries grows, trying to remember what Query1 or Query18 does can be impossible. Using descriptive names takes out the guesswork.

Always save your queries and do not rely on OpenOffice Base to automatically save it for you. In most cases, you will be asked to save any newly created or edited queries. However, manually saving prevents any possible glitches from not prompting you to save.

Post from:

Create SQL Queries in OpenOffice Base