[Solved] SQL syntax

Creating tables and queries
Post Reply
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

[Solved] SQL syntax

Post 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!

Thanks
Last edited by LJ Bettona on Mon Jan 07, 2008 5:03 pm, edited 1 time in total.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
TerryE
Volunteer
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: SQL syntax

Post 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.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: SQL syntax

Post 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?

kabing

P.S. I found the book "SQL Queries for Mere Mortals" quite helpful as an introduction to SQL.
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: SQL syntax

Post 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)
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: SQL syntax

Post 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

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

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).

kabing
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
User avatar
LJ Bettona
Posts: 58
Joined: Fri Jan 04, 2008 12:31 pm
Location: Italy

Re: SQL syntax

Post 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.
iMac G5, OS 10.4.11
OO 2.4, NeoOffice 3 patch 1
OOo 2.2.X on Mac OSx other + Actually NeoOffice 3 patch 1
User avatar
kabing
Volunteer
Posts: 678
Joined: Fri Nov 23, 2007 12:05 am
Location: Midwest, USA

Re: SQL syntax

Post 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.

kabing

Edit: You might find this tutorial on Combo Boxes helpful. It is written for NeoOffice, an independent Mac port/fork of OpenOffice.org. Since NeoOffice is based on OpenOffice.org, the instructions should work fine for OpenOffice.org, 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.
NeoOffice (app store version) OpenOffice.org 4.1 and LibreOffice 4.3 on Mac OS X El Capitan
OpenOffice.org 4.1.2 on Windows 10 (Previously on Vista)
Post Reply