Syntax error in from clause

[Microsoft][ODBC Text Driver] Syntax error in FROM clause.

Troubleshooting

Problem

Problem Number Error Error number -3989587 DMS-E-GENERAL, A General Exception has occurred during operation ‘prepare request’. DMS-E-DBPARSER, The underlying database detected an error during processing of the SQL request. [Microsoft][ODBC Text Driver] Syntax error in FROM clause. when querying an ODBC datasource using the Microsoft ODBC Text Driver. Solution Description This error is due to an issue with the ODBC driver. Microsoft have documented this issue in their Knowledge Base articles #Q124322, Q125959 and Q124319. Please contact Microsoft for further information on this issue. As a workaround it may be possible to Qualify the Tables Less in the Catalog Tables dialog window. It may also be necessary to change the delimiter type in the ODBC drivers options, and to use Escape Metacharacters when retrieving the data. This option is also set in the Options for the ODBC driver. The problem is due to the driver not supporting quoted schema’s or tables thus to eliminate the problem the attach must be made using the connection parameter DELIMIT=COL. Microsoft offers the following as a workaround. Please contact Microsoft for further information on this technique. These workarounds are not set within Impromptu but are for the Driver. Method 1: Name the range something other than «Database» or «Table.» Method 2: If you are accessing data from a Microsoft Excel 5.0 workbook that contains a global range named «Database» or «Table,» define it as a local range name, such as «Sheet1!Database.» Note that making the name a local one will cause the table name to be displayed with the sheet name and then the range name: Sheet1!Database. Method 3: Add brackets ([) around the words «Database» or «Table.» For example, to select all records from a table called Database, follow these steps: a. In Microsoft Query, click the SQL button on the toolbar. This displays the SQL dialog box. b. In the dialog box, type the following SQL statement: SELECT * FROM [Database] c. Choose OK. A message will appear stating that the query cannot be represented graphically. d. Choose Yes to continue. This displays only the Data pane in the query window.

Resolving The Problem

This error is due to an issue with the ODBC driver. Microsoft have documented this issue in their Knowledge Base articles #Q124322, Q125959 and Q124319. Please contact Microsoft for further information on this issue.

As a workaround it may be possible to Qualify the Tables Less in the Catalog Tables dialog window. It may also be necessary to change the delimiter type in the ODBC drivers options, and to use Escape Metacharacters when retrieving the data. This option is also set in the Options for the ODBC driver.

The problem is due to the driver not supporting quoted schema’s or
tables thus to eliminate the problem the attach must be made using the
connection parameter DELIMIT=COL.

Microsoft offers the following as a workaround. Please contact Microsoft for further information on this technique. These workarounds are not set within Impromptu but are for the Driver.

Method 1: Name the range something other than «Database» or «Table.»

Method 2: If you are accessing data from a Microsoft Excel 5.0 workbook
that contains a global range named «Database» or «Table,» define
it as a local range name, such as «Sheet1!Database.» Note that
making the name a local one will cause the table name to be
displayed with the sheet name and then the range name:
Sheet1!Database.

Method 3: Add brackets ([) around the words «Database» or «Table.» For
example, to select all records from a table called Database,
follow these Steps:a. In Microsoft Query, click the SQL button on the toolbar.

This displays the SQL dialog box.

b. In the dialog box, type the following SQL statement:

SELECT * FROM [Database]

A message will appear stating that the query cannot be
represented graphically.

d. Choose Yes to continue.

This displays only the Data pane in the query window.

Источник

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!

Источник