Error 107

Severity

Message text

The column prefix '%.*s' does not match with a table name or alias name used in the query. Either the table is not specified in the FROM clause or it has a correlation name which must be used instead.

Explanation

Tables are specified in the from clause of a query. When Adaptive Server parses a query prior to execution, the name by which it knows the table is in the from clause—the table name if given alone, or a correlation name (alias) if one was specified. For example:

1> select c.cityname from cities c
2> go

In the above query, the table is known as c for purposes of the query. Names specified in other clauses refer back to this name to determine what table is intended. Error 107 can be raised:

If a column prefix intended as a table name does not match any table name specified in the from clause.
If a query does not use correlation names consistently. In Adaptive Server, queries that include correlation names must conform to ANSI requirements. Statements that specify correlation names but do not use them consistently raise error 107.

These restrictions apply to views as well as real database tables.

Action

Check your query for these errors and take corrective action:

Check for a typing error; for example:

1> select * from titles
2> where title.code = 205
3> go

Msg 107, Level 15, State 1:
Server 'mfg', Line 2:
The column prefix 'title' does not match with a table name or alias name used

The column name in the where clause should be titles.code. This statement is also incorrect:

1> select t2.title_id from titles t1

The correct statement is:

1> select t1.title_id from titles t1

Ensure that correlation names are used correctly. For example, this statement is incorrect:
```
1> select title_id 
2> from titles t
3> where titles.type =  "french_cook"
```
The where clause can not use titles, because the from clause defines a correlation name for the table. The correct query is:
```
1> select title_id 
2> from titles t
3> where t.type = "french_cook"
```

Additional information

The special case exists where a query that returns error 107 may report no error when the same type of correlation is used in a subquery. For example:

1> select * from mytable
2> where columnA =
3> (select min(columnB) from mytable m
4> where mytable.columnC = 10)

This query is a correlated subquery, and mytable.columnC refers to the outer table mytable. This query works because the same table is referred to in the inner and outer queries. In general, however, correlated subqueries can also generate error 107 when correlation names are used incorrectly.

Versions in which this error is raised

All versions