Syntax error in sql expression

Syntax error in sql expression что это

Post by Vampyer » Tue Jul 05, 2016 11:18 am

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?

Re: Syntax Error in SQL Expression?

Post by Imagination *-* » Tue Jul 05, 2016 11:39 am

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.

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

I hope it helps!

Re: Syntax Error in SQL Expression?

Post by charlie.it » Tue Jul 05, 2016 12:09 pm

charlie
Italian AOO Admin
macOS 10.13.6 High Sierra: Open Office 4.1.13 — LibreOffice 7.2.6.2

Re: Syntax Error in SQL Expression?

Post by Vampyer » Tue Jul 05, 2016 12:11 pm

Imagination *-* wrote: 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.

I hope it helps!

Re: Syntax Error in SQL Expression?

Post by Imagination *-* » Tue Jul 05, 2016 12:19 pm

Re: Syntax Error in SQL Expression?

Post by charlie.it » Tue Jul 05, 2016 12:21 pm

charlie
Italian AOO Admin
macOS 10.13.6 High Sierra: Open Office 4.1.13 — LibreOffice 7.2.6.2

Re: Syntax Error in SQL Expression?

Post by Vampyer » Tue Jul 05, 2016 12:35 pm

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. :/

Re: Syntax Error in SQL Expression?

Post by eremmel » Tue Jul 05, 2016 9:08 pm

@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 «

Re: Syntax Error in SQL Expression?

Post by charlie.it » Wed Jul 06, 2016 6:51 am

charlie
Italian AOO Admin
macOS 10.13.6 High Sierra: Open Office 4.1.13 — LibreOffice 7.2.6.2

Источник

SQL Errors: Five Common SQL Mistakes

As you learn SQL, watch out for these common coding mistakes

You’ve written some SQL code and you’re ready to query your database. You input the code and …. no data is returned. Instead, you get an error message.

Don’t despair! Coding errors are common in any programming language, and SQL is no exception. In this article, we’ll discuss five common mistakes people make when writing SQL.

The best way to prevent mistakes in SQL is practice. LearnSQL.com offers over 30 interactive SQL courses. Try out our SQL Practice track with 5 courses and over 600 hands-on exercises.

Watch Your Language (and Syntax)

The most common SQL error is a syntax error. What does syntax mean? Basically, it means a set arrangement of words and commands. If you use improper syntax, the database does not know what you’re trying to tell it.

To understand how syntax works, we can think of a spoken language. Imagine saying to a person “Nice dof” when you mean “Nice dog”. The person does not know what “dof” means. So when you tell your database to find a TABEL instead of a TABLE, the database does not know what it needs to do.

People tend to make the same kinds of syntax mistakes, so their errors are usually easy to spot and very much the same. After you read this article, you should be able to remember and avoid (or fix) these common mistakes. Knowing what errors to look for is very important for novice SQL coders, especially early on. New coders tend to make more mistakes and spend more time looking for them.

The types of SQL errors we will look at are:

1. Misspelling Commands
2. Forgetting Brackets and Quotes
3. Specifying an Invalid Statement Order
4. Omitting Table Aliases
5. Using Case-Sensitive Names

Ready? Let’s start.

SQL Errors:

1. Misspelling Commands

This is the most common type of SQL mistake among rookie and experienced developers alike. Let’s see what it looks like. Examine the simple SELECT statement below and see if you can spot a problem:

If you run this query, you’ll get an error which states:

Each database version will tell you the exact word or phrase it doesn’t understand, although the error message may be slightly different.

What is wrong here? You misspelled FROM as FORM. Misspellings are commonly found in keywords (like SELECT, FROM, and WHERE), or in table and column names.

Most common SQL spelling errors are due to:

• “Chubby fingers” where you hit a letter near the right one: SELEVT or FTOM or WJIRE
• “Reckless typing” where you type the right letters in the wrong order: SELETC or FORM or WHEER

Solution:

Use an SQL editor that has syntax highlighting: the SELECT and WHERE keywords will be highlighted, but the misspelled FORM will not get highlighted.

If you’re learning with interactive SQL courses in LearnSQL.com , the code editor puts every SELECT statement keyword in light purple. If the keyword is black, as it is with any other argument, you know there’s a problem. (In our example, FORM is black).

So if we correct our statement we get:

The keyword is now the right color and the statement executes without an error.

2. Forgetting Brackets and Quotes

Brackets group operations together and guide the execution order. In SQL (and in all of the programming languages I use), the following order of operations …

… is not the same as:

Can you figure out why?

A very common SQL mistake is to forget the closing bracket. So if we look at this erroneous statement :

We get an error code with the position of the error (the 102nd character from the beginning):

Remember: brackets always come in pairs.

The same is true with single quotes ( ‘ ‘ ) or double quotes ( ” ” ). There is no situation in SQL where we would find a quote (either a single quote or a double quote) without its mate. Column text values can contain one quote ( e.g. exp.last_name = «O’Reilly» ) and in these situations we must mix two types of quotes or use escape characters. ( In SQL, using escape characters simply means placing another quote near the character you want to deactivate – e.g. exp.last_name = ‘O’’Reilly. )

Solution:

Practice, practice, practice. Writing more SQL code will give you the experience you need to avoid these mistakes. And remember people usually forget the closing bracket or quotation mark. They rarely leave out the opening one. If you’re running into problems, take a close look at all your closing punctuation!

3. Invalid statement order

When writing SELECT statements, keep in mind that there is a predefined keyword order needed for the statement to execute properly. There is no leeway here.

Let’s look at an example of a correctly-ordered statement:

There’s no shortcut here; you simply have to remember the correct keyword order for the SELECT statement:

• SELECT identifies column names and functions
• FROM specifies table name or names (and JOIN conditions if you’re using multiple tables)
• WHERE defines filtering statements
• GROUP BY shows how to group columns
• HAVING filters the grouped values
• ORDER BY sets the order in which the results will be displayed

You cannot write a WHERE keyword before a FROM , and you can’t put a HAVING before a GROUP BY . The statement would be invalid.

Let’s look at what happens when you mix up the statement order. In this instance, we’ll use the common SQL error of placing ORDER BY before GROUP BY :

The error message we see is pretty intimidating!

Solution:

Don’t be discouraged! You can see that all of the keywords are highlighted correctly and all the quotations and brackets are closed. So now you should check the statement order. When you’re just beginning your SQL studies, I suggest using a SELECT order checklist. If you run into a problem, refer to your list for the correct order.

4. Omitting Table Aliases

When joining tables, creating table aliases is a popular practice. These aliases distinguish among columns with the same name across tables; thus the database will know which column values to return. This is not mandatory when we’re joining different tables, since we can use the full table names. But it is mandatory if we join a table to itself.

Suppose we’re writing an SQL statement to find an exhibition’s current location and the location from the previous year:

The database would return an error:

Note: Whenever you encounter “ambiguous column name” in your error message, you surely need table aliases.

The correct statement (with aliases) would be:

Solution:

Practice using table aliases for single-table SELECT statements. Use aliases often – they make your SQL more readable.

5. Using Case-Sensitive Names

This error only occurs when you need to write non-standard names for tables or database objects.

Let’s say that you need to have a table named LargeClient and for some reason you add another table called LARGECLIENT. As you already know, object names in databases are usually case-insensitive. So when you write a query for the LargeClient table, the database will actually query LARGECLIENT.

To avoid this, you must put double quotes around the table name. For example:

When creating a table, you will need to use double quotes if:

• The table will have a case-sensitive name.
• The table name will contain special characters. This includes using a blank space, like “Large Client”.

Solution:

Avoid using these names if you can. If not, remember your double quotes!

Everybody Makes SQL Mistakes

Those are the five most common errors in SQL code. You’ll probably make them many times as you learn this language. Remember, everybody makes mistakes writing code. In fact, making mistakes is a normal and predictable part of software development.

So don’t be discouraged. When you make mistakes in the future, try to analyze your code in a structured way. With a structured analysis, you can find and correct your errors quicker.

If you would like to learn about some other syntactic mistakes that I’ve not included here, please let me know. In an upcoming article, we’ll look at non-syntactic errors. These return or modify data and are therefore much more dangerous. Subscribe to our blog so you won’t miss it!

Источник

SQLShack

Common SQL syntax errors and how to resolve them

In the SQL Server Management Studio, errors can be tracked down easily, using the built in Error List pane. This pane can be activated in the View menu, or by using shortcuts Ctrl+ and Ctrl+E

The Error List pane displays syntax and semantic errors found in the query editor. To navigate directly to the SQL syntax error in the script editor, double-click the corresponding error displayed in the Error List

SQL Keyword errors

SQL keyword errors occur when one of the words that the SQL query language reserves for its commands and clauses is misspelled. For example, writing “UPDTE” instead of “UPDATE” will produce this type of error

In this example, the keyword “TABLE” is misspelled:

As shown in the image above, not only the word “TBLE” is highlighted, but also the words around it. The image below shows that this simple mistake causes many highlighted words

In fact, there are total of 49 errors reported just because one keyword is misspelled

If the user wants to resolve all these reported errors, without finding the original one, what started as a simple typo, becomes a much bigger problem

It’s also possible that all SQL keywords are spelled correctly, but their arrangement is not in the correct order. For example, the statement “FROM Table_1 SELECT *” will report an SQL syntax error

Arrangement of commands

The wrong arrangement of keywords will certainly cause an error, but wrongly arranged commands may also be an issue

If the user, for example, is trying to create a new schema into an existing database, but first wants to check if there is already a schema with the same name, he would write the following command

However, even though each command is properly written, and is able to run separately without errors, in this form it results in an error

As the error message states, CREATE SCHEMA command has to be the first command that is given. The correct way of running this commands together looks like this

Using quotation marks

Another common error that occurs when writing SQL project is to use double quotation marks instead of single ones. Single quotation marks are used to delimit strings. For example, double quotation marks are used here instead of single ones, which cause an error

Replacing quotation marks with the proper ones, resolves the error

There are situations where double quotation marks need to be used, for writing some general quotes, for example

As shown in the previous example, this will cause an error. But, this doesn’t mean that double quotes can’t be used, they just have to be inside the single quotes. However, adding single quotes in this example won’t solve the problem, but it will cause another one

Since there is an apostrophe inside this quote, it is mistakenly used as the end of a string. Everything beyond is considered to be an error

To be able to use an apostrophe inside a string, it has to be “escaped”, so that it is not considered as a string delimiter. To “escape” an apostrophe, another apostrophe has to be used next to it, as it is shown below

Finding SQL syntax errors

Finding SQL syntax errors can be complicated, but there are some tips on how to make it a bit easier. Using the aforementioned Error List helps in a great way. It allows the user to check for errors while still writing the project, and avoid later searching through thousands lines of code

Another way to help, is to properly format the code

This can improve code readability, thus making the search for errors easier

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University.

She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014.

Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring.

• Using custom reports to improve performance reporting in SQL Server 2014 – running and modifying the reports — September 12, 2014
• Using custom reports to improve performance reporting in SQL Server 2014 – the basics — September 8, 2014
• Performance Dashboard Reports in SQL Server 2014 — July 29, 2014

About Milena Petrovic

Milena is a SQL Server professional with more than 20 years of experience in IT. She has started with computer programming in high school and continued at University. She has been working with SQL Server since 2005 and has experience with SQL 2000 through SQL 2014. Her favorite SQL Server topics are SQL Server disaster recovery, auditing, and performance monitoring. View all posts by Milena «Millie» Petrovic

Источник

Whenever you try to access a table with a method that has an underlying SQL representation of "select * from tablename" you get an error dialog which says: "The data content could not be loaded. Syntax error in SQL expression." Clicking the "More" button in the error dialogs leads through the following: SQL Status: HY000 Error Code: 1000 Syntax error in SQL expression select * from "people" syntax error, unexpected INVALIDSYMBOL, expecting $end, Invalid symbol NOTE: people was the name of a table I created in a database to test this. The error occurs reproducibly under a number of circumstances. The easiest way to cause it to happen is to open an existing database, going to the table editor, and then double-clicking on any given table to see/edit the data. It happens with forms or queries that load an entire table, i.e. that have no where clause. If you edit a query in SQL view, you can see the error with select * from tablename. However, if you type select * from tablename where 1=1, you get the results that you expected for the previous query. This error occurs for me in an existing hsqldb, a brand new hsqldb created just to test this, and a MySQL database accessed via JDBC and mysql-connector 5.0. Comment 1 eric_openoffice 2008-03-25 08:27:23 UTC Confirming the issue using DEV300_m4 under Mac OS X 10.5.2 on Mac Intel. Following the given steps the issue is reproducible. Comment 2 Mechtilde 2008-03-25 11:24:11 UTC Does it also occurs without quoting marks? Comment 3 dyrcona 2008-03-25 13:37:25 UTC Yes, it occurs without quote marks. All you have to do is double-click on a table in the Tables sheet to view its data. Comment 4 Mechtilde 2008-03-25 13:42:21 UTC can you attach an example database with this problem. so I can try to reproduse it on my machine? Comment 6 dyrcona 2008-03-25 14:37:04 UTC I added the database that I created to test this yesterday. However, with any database that you open with Base from DEV300_m4 on a Mac running Leopard, you get this problem. I can't say whether or not it affects other platforms or OS releases, as this is the only one where I've used m4. Comment 7 Uwe Altmann 2008-03-25 18:25:04 UTC select * from people Works in mahos DEV300m2 (OOo_3.0.0_080314_MacOSXIntel_install_de) with your testdb.odb (four entries, right?) and an own one HSQLDB too Comment 8 dyrcona 2008-03-25 18:45:26 UTC Ok. If it's working in m2 and not working in m4? What changed? sb83? Comment 9 Frank Schönheit 2008-03-26 12:01:05 UTC I know two issues which could cause this: First, we once had a problem that our syntax file, when compiled with a recent flex version, produced nonsense, but didn't when compiled with an older version. This was fixed quite a while ago, and I doubt it re-appeared in our source (there was no significant change between m2 and m4, not even in sb83). Second, the flex coming with Leopard has a problem: When fed with our input file, it produces code which is, in exactly one line, different from the code produced by an flex downloaded from the project page. Unfortunately, this one line makes our parser to fail. Both of the reasons mentioned above are known to cause the problem described here. I would assume that the m4 build we talk about was produced in a different environment than the m2 build, the latter using a flex from Tiger, the former using a flex from Leopard. Comment 10 dyrcona 2008-03-26 13:34:23 UTC Yes, in my case, the m4 build was done on my Leopard machine. Tiger has flex version 2.5.4 and Leopard has 2.5.33. I can install 2.5.35 and see if that makes a difference. If not, then I could manually install 2.5.4 on Leopard and make sure it is my path before /usr/bin/flex when building. I'll try changing flex versions and report the results. Oh, and I diffed m2 and m4 last night, and there were no significant changes in dbaccess, just some string resource files changed. Comment 11 Frank Schönheit 2008-03-26 14:18:34 UTC (the interesting project for the diff would be connectivity, not dbaccess) 2.5.4 is known to work, so I assume "downgrading" to it would solve the problem. Note that an out-of-the-box 2.5.33 is also known to work (meanwhile, it formerly didn't, this was the first problem I mentioned above, but it is fixed now on OOo side). What causes the problem is the 2.5.33 shipped with Leopoard. When you change your flex version, you need to rebuild connectivity/source/parse and connectivity/source/dbtools. (touching sqlflex.l should suffice, but to be on the safe side you could do a complete rebuild.) Comment 12 dyrcona 2008-03-26 19:08:19 UTC I did a complete rebuild from a fresh checkout with flex 2.5.35 installed from MacPorts. The problem has vanished. Base is working for me. I'll have to make a note about flex versions on my Leopard Build page. Comment 13 eric_openoffice 2008-03-26 22:57:26 UTC Confirmed. If I use flex 2.5.35 out of MacPorts the tables are accesable. I only rebuild connectivity and will do a clean build with the same flex once I have the time to do so. @fs: Yes. m2 was build using Tiger. So you're right that the bug is caused by Leopards flex. Is there a chance to get this fixed for the Leopard flex? Comment 14 Frank Schönheit 2008-03-27 07:35:10 UTC I know this is reported to the Mac developers, though not yet in their bug tracking system, but via other channels. Just yesterday I asked our side of the contact to check the status of this issue. Comment 15 christoph.lukasiak 2008-09-01 15:07:40 UTC does this problem still occure in a current version? Comment 16 dyrcona 2008-09-01 23:21:31 UTC I can't say. I am building with a more recent version of flex, 2.5.35, than comes with Leopard, 2.5.33. I don't have the problem any more, but I haven't build with an older flex. Guess, I could try that later this week (time permitting) and let you know. Comment 17 dyrcona 2008-09-03 00:22:38 UTC I just tried DEV300_m29 with CWS cloph11 with the flex that comes with Leopard/Xcode 3, and not a more recent one from Mac Ports, and the problem still occurs. When I try a select statement without a where clause, the parser tells me there is an error in SQL statement. The following works: select * from country where 1=1 The following don't: select * from country where 1 select * from country I am not sure that anything in OO.o DBA needs fixing, though. After all, the problem only seems to occurs with certain versions of flex, 2.5.4 and 2.5.35 work just fine. Comment 18 christoph.lukasiak 2008-09-15 12:40:00 UTC => close Comment 19 christoph.lukasiak 2008-09-15 12:40:23 UTC .