Page 1 of 1

[Solved] SQL syntax

PostPosted: Sun Jan 06, 2008 2:15 pm
by LJ Bettona
Hi, I'm new to SQL and creating relational databases. I've downloaded and printed the relevant chapter of Getting Started in Base and have followed the creation of a database and a form. There is a table called Payment Type. It contains a field called Type. The SQL command I was told to type in to create a dropdown list to display the contents of this field is:

SELECT "Type", "Type" FROM "Payment Type"

When I tried to open the form to enter some test data (just to see how it worked), I got an SQL error message saying basically that the syntax was wrong and the whole thing crashed. I have recovered the database and its form but can find no errors in my typing.
So, what went wrong? As this is the first SQL command I have typed, I haven't a clue as to how the syntax works. One thing that is not clear from the text in the manual is the location of space bars in the command. Should there be a space between the , and the alias, for instance? Yes, I used double quotes. Yes, I checked the name of the table and the name of the field. (It's not the first time I've created a database, just the first time I've used these tools.) Yes, I put the caps where they should be both in the commands and in the names and alias.

Any help would be gratefully received as the whole reason I started on this is to create a database which would reduce data entry by using reference tables. Not gonna get anywhere until I've got that command right, am I?
Also, any suggestions for a book for teach yourself SQL? Looks like I'm gonna need it!


Re: SQL syntax

PostPosted: Sun Jan 06, 2008 9:09 pm
by TerryE
The database engine used by Base is called HSQLDB. An ODB file is just a zipped constainer containing the XML definitions of your macros, forms, etc. and the HSQLDB database. If you want documentation on it google "HSQLDB Documentation" and its the first hit.

Also a good what to start understanding SQL is to use the wizards to build your views and queries and then look at them with the SQL view.

Re: SQL syntax

PostPosted: Mon Jan 07, 2008 6:35 am
by kabing
What page or section of the getting Started with Base are you referring to?

Where, exactly, did you type this SQL "sentence."? SQL is used in lots of different aspects of Base.

Are you wanting to create a list box or a combo box in your form?


P.S. I found the book "SQL Queries for Mere Mortals" quite helpful as an introduction to SQL.

Re: SQL syntax

PostPosted: Mon Jan 07, 2008 12:08 pm
by LJ Bettona
I am using 'Getting Started With Base'. I created the tables for the Vacations database as instructed from pgs 8 - 15. Then I started designing a form using the wizard. On pg 25 the instructions for 'Replace fields with other fields' begin (step 6). The following actions were done:
a payment field was clicked
Replace with >List Box was selected from the list of replacement fields
In the General properties window Dropdown was changed from No to Yes
In the Data properties window Valuelist was changed to sql and
SELECT "Type, "Type" FROM "Payment Type" was entered in the list contents

As in the first post, I am aware that syntax is critical and find it difficult to determine from the printed instructions where space bars should and should not go. The error message is extremely uninformative and gives no clue as to how to proceed.

There are other anomalies but they are for another post once this is sorted.

Thanks for the help (and the book suggestion; sounds like what I need)

Re: SQL syntax

PostPosted: Mon Jan 07, 2008 4:50 pm
by kabing
Thank you very much for the specific page numbers. Turns out the copy of Getting Started With Base that I had was considerably out of date, which is why I couldn't find the references initially. I have now downloaded the new version, and found the instructions you are following.

I am a novice when it comes to SQL, but the syntax

Code: Select all   Expand viewCollapse view
SELECT "Type", "Type" FROM "Payment Type"

seems odd to me. I don't understand why they are having you list the Type field twice. It is my understanding that you must give a field an alias if you are going to select it a second time.

Two things to try.

1) check to make sure you typed the line exactly as above (copied from the Getting Started with Base pdf) In the line you provided in your most recent post, you left out a " after the first Type.

2)If that doesn't fix it, try this syntax:

Code: Select all   Expand viewCollapse view
SELECT "Type" FROM "Payment Type"

Beyond that, someone else will have to help you. (And if someone else posts before you try my suggestions, try their suggestion first. There are many Base users on this forum with more experience than I).


Re: SQL syntax

PostPosted: Mon Jan 07, 2008 5:01 pm
by LJ Bettona
Sorry, that was a typo. I did put in all the ".

I have since created a new database for my vast book collection and tried typing the syntax without the space between the equivalent of the two uses of type. (For some reason the guide wants to use an alias, maybe to let the new user know that they exist.) Anyway, it worked. I am able to save the table. So far I haven't tried to use the form with this SQL on it. I am typing in author's names at the moment. Once that is complete, I'll try to access the reference table from the book title input form. Wish me luck!

My next challenge is to check entries against existing ones so I don't inadvertently enter an author or a title I have already entered! I think I'll wait for my book to arrive before I tackle that one!

Thanks for the help and the book suggestion.

Re: SQL syntax

PostPosted: Mon Jan 07, 2008 6:12 pm
by kabing
I'm glad you got it working.

LJ Bettona wrote:My next challenge is to check entries against existing ones so I don't inadvertently enter an author or a title I have already entered! I think I'll wait for my book to arrive before I tackle that one!

You might want to try a combo box for this.
-Replace the "plain" field with a combo box.
-In the properties window, Set the Data Field to Author.
-Set Type of List Contents to Sql
-Set List content to: SELECT DISTINCT "Author" FROM "TableName" (just make sure to use the right field and table names).

This will let you add new entries, but will pull up an existing value when you start typing.

That may not be your best option, depending on the design of your database. If you want some more suggestions for that issue, I would suggest starting a new thread. Be sure to spell out your tables and fields if you do so.


Edit: You might find this tutorial on Combo Boxes helpful. It is written for NeoOffice, an independent Mac port/fork of Since NeoOffice is based on, the instructions should work fine for, if you keep the following things in mind:

1) references to the command key in a Neo tutorial are the equivalent to the control key in OOo. (i.e. Command-C becomes Control-C) Note that I'm not sure if this is true with OOo for X11.
2) control-clicking is the same as right clicking
3) icons sometimes look different, as NeoOffice 2.2.2 has a custom icon set.