error 4408: 16 table limit??

[Ed Keen]

I have a fairly complex set of statistical views and when I try to combine them together into one "master" view, I get the following error:

Msg 4408, Level 19, State 1
The query and the views in it exceed the limit of 16 tables.

I have not been able to find anything in books online or the kb addressing this issue. Is there a way to increase this limit? The first question I had of myself is, "Is there a way to make the view and underlying views more efficient?" But I have been coding sql for years now and I do not think there is much of a way to reduce the number of tables. It is just a darn complex query!

Any help beyond "simplify your sql statement" would be of great help.

Thanks,
Ed Keen

[Don Romano]

Don`t have an answer for you except to say `redesign`...

According to Books OnLine under the Select statement:

"FROM
Indicates the specific table(s) and view(s) that are used in the SELECT statement. FROM is required except when the select_list contains only constants, variables, and arithmetic expressions (no column names). The FROM clause allows a maximum of 16 tables and views. Tables in subqueries are counted as part of this total."

We just hit his problem here as well... we`re having to look a cludgy ways to get around it such as using #temp tables or creating intermediate tables that denormalize the database model for these complex queries.

If you find out otherwise please let me know...

[Jonathan Yang]

There is 16 table limit to the SQL statement in SQL 6.5. But SQL 7.0 will allow much more. If you can wait for a few weeks...



On 8/7/98 10:18:01 AM, Ed Keen wrote:
> I have a fairly complex set of statistical views and when I try to combine
> them together into one "master" view, I get the following error:

Msg
> 4408, Level 19, State 1
The query and the views in it exceed the limit of
> 16 tables.

I have not been able to find anything in books online or the
> kb addressing this issue. Is there a way to increase this limit? The
> first question I had of myself is, "Is there a way to make the view and
> underlying views more efficient?" But I have been coding sql for years now
> and I do not think there is much of a way to reduce the number of tables.
> It is just a darn complex query!

Any help beyond "simplify your sql
> statement" would be of great help.

Thanks,
Ed Keen