[Solved] Syntax Error in SQL Expression?

[Vampyer]

Hey guys,

I'm trying to use the query wizard to sort through and, more importantly, omit some of the data in what will be a huge database. whenever I try to use the wizard though, I get the following error:

SQL Status: HY000
Error code: 1000

SELECT "AssetTable"."Staff Name:" AS "Staff Name:", "AssetTable"."Staff Office:" AS "Staff Office:" FROM "AssetTable" "AssetTable" WHERE (Staff Name: = 'Oxford Staff')

So, two questions. How do I use the Query Wizard in a way that will actually work? and... what's the point in having a wizard if it doesn't work?

Thanks,

[Imagination *-*]

I'm not a pro but I believe your SQL structure is not correct.

Here is a sample SQL query extracted from my database for your reference.

Code: Select all

SELECT "MainTableID" AS "Entry No.", "Customer", "Required.Date" AS "Required Date", "Completion.Date" AS "Completion Date" FROM "MainTable.CopyPasteHere" WHERE "Required.Date" = :Date

It will also help if you could give us a screenshot of your query wizard

I hope it helps!

[charlie.it]

FROM "AssetTable" "AssetTable"

Perhaps this is a duplicate.

[Vampyer]

I'm not a pro but I believe your SQL structure is not correct.

Here is a sample SQL query extracted from my database for your reference.

Code: Select all

SELECT "MainTableID" AS "Entry No.", "Customer", "Required.Date" AS "Required Date", "Completion.Date" AS "Completion Date" FROM "MainTable.CopyPasteHere" WHERE "Required.Date" = :Date"

I hope it helps!

Thanks for the input but I'm still struggling. The thing is, I'm not putting any syntax in so to speak, it's all the wizard... what's the point of having a wizard if it doesn't bloody work?

[Imagination *-*]

In my opinion the wizard is useless (I've rarely used it even when I knew nothing of OpenOffice base when I was first starting out) . Best to go the non-wizard way as recommended by many others.

[charlie.it]

Did you try this correct code? In SQL mode, without wizard.

Code: Select all

SELECT "AssetTable"."Staff Name:" AS "Staff Name:", "AssetTable"."Staff Office:" AS "Staff Office:" FROM "AssetTable" WHERE "Staff Name:" = 'Oxford Staff'

Edit: Is Oxford Staff a field name or a string?

[Vampyer]

Did you try this correct code? In SQL mode, without wizard.

Code: Select all

SELECT "AssetTable"."Staff Name:" AS "Staff Name:", "AssetTable"."Staff Office:" AS "Staff Office:" FROM "AssetTable" WHERE "Staff Name:" = 'Oxford Staff'

Edit: Is Oxford Staff a field name or a string?

THAT WORKED! I'll have to compare the wizard's code to the one you just gave me and try it with some different perimeters but that worked. Thanks.

The thing is, I used the wizard because I'm volunteering for a company at the moment and won't be around for very long... so I thought the wizard would be a good way for them to search through the database and have something printable at the end of it. Maybe I can just leave some basic search and sort queries for them and they can change the fields as and when needed. :/

[eremmel]

@Vampyer
When you use table/field names with a mix of upper/lower/space/special-chars then you have to put your table/field names between " and ".

@Charlie.it
The 'double' table name in the FROM-clause is a good habit. The left-hand-side is the table name and the right-hand-side is called the alias. The alias should say something about role of the referenced table. Example: we have a table Member that contain family members with a self-reference to the two parents. FROM-clause that uses this relation for showing a member with both the parents might have look like: FROM "Member" ME, "Member" MOTHER, "Member" FATHER. Note: the aliases are in capitals so no need to place it between " and "

[charlie.it]

@eremmel
I use aliases when they are needed, but not when they are equal to the field name or table name.