16 Table Max in SQL 6.5 3

[nevets72]

I just attempted to create a Crystal Report, via a view, but I got the following message:

The query and the views in it exceed the limit of 16 tables.

I'm not sure what options I have.....I really can't create a table based on the functionality of the application I'm using. What can I do?

The view in question is as follows:

CREATE VIEW Rpt_Actuals (Month, Pool_Category_ID, Staffing_Pools_ID, Project_ID, Manager_ID, PIV_ID, Task_ID, Division_ID, Group_ID, Team_ID, Job_Code_ID, Employee_ID, Hours, Total_Hours, MA_Indicator) AS
SELECT Manpower.Month, Pool_Category.Pool_Category_ID, Staffing_Pools.Staffing_Pools_ID, Projects.Project_ID, Employees_1.Employee_ID, Task.PIV_ID, Task.Task_ID, Employees.Division_ID, Employees.Group_ID, Employees.Team_ID, Employees.Job_Code_ID, Employees.Employee_ID, Sum(Manpower.Hours), Totals.Hours, "N"
FROM Access_Code Access_Code, Employees, Employees Employees_1, Manpower Manpower, Pool_Category Pool_Category, Project_Categories Project_Categories, Projects Projects, Staffing_Pools Staffing_Pools, Task Task, Totals Totals
WHERE Employees.Employee_ID = Manpower.Employee_ID AND Task.PIV_ID = Manpower.PIV_ID AND Task.Task_ID = Manpower.Task_ID AND Projects.Project_ID = Project_Categories.Project_ID AND Access_Code.Access_Code_ID = Project_Categories.Access_Code_ID AND Access_Code.Access_Code_ID = Task.Access_Code_ID AND Staffing_Pools.Staffing_Pools_ID = Project_Categories.Staffing_Pools_ID AND Pool_Category.Pool_Category_ID = Staffing_Pools.Pool_Category_ID AND Totals.Employee_ID = Employees.Employee_ID AND Totals.Month = Manpower.Month AND Employees_1.Employee_ID = Projects.Employee_ID AND (Employees.Status_ID IN (1, 2, 3, 4, 5)) and Projects.Project_ID != 435
GROUP BY Manpower.Month, Pool_Category.Pool_Category_ID, Staffing_Pools.Staffing_Pools_ID, Projects.Project_ID, Employees_1.Employee_ID, Task.PIV_ID, Task.Task_ID, Employees.Division_ID, Employees.Group_ID, Employees.Team_ID, Employees.Job_Code_ID, Employees.Employee_ID, Totals.Hours
Union All
SELECT * FROM dbo.Rpt_Actuals_MA

 

[chiph]

Can you successfully create the view using Query Analyzer?

Chip H.

 

[bperry]

Do you get the same message if you drop the:

Union All
SELECT * FROM dbo.Rpt_Actuals_MA

 

[nevets72]

That's where I'm confused.....I am able to run the entire query successfully in the query analyzer, but it bombs when I try to create the report. Any ideas?

 

[bperry]

If you like, you could try dropping the entire UNION section from your query:

Union All
SELECT * FROM dbo.Rpt_Actuals_MA

and see if the remaining part (all the rest of it) runs from the report Even though your query runs okay from QA, I'm wondering if the Union is screwing up the CR report. (Don't know if that's it here, but I've seen simialr things with CR before.)

bp

 

[arrowhouse]

Don't know the exact way crystal reports works but can you see the SQL it generates?

You have 10 tables in the above view, + 1 for the view itself making 11. If crystal reports is linking to other tables to get the information you have requested you may well break the magic 16.

ALSO

is the dbo.Rpt_Actuals_MA a view or a table?

what about the others

Access_Code Access_Code,
Employees,
Employees Employees_1,
Manpower Manpower,
Pool_Category Pool_Category,
Project_Categories Project_Categories,
Projects Projects, Staffing_Pools Staffing_Pools,
Task Task,
Totals Totals

If any of these are views you need to count:
1) the number of tables that each view consists of
2) plus one for each view used
3) then add one for each table
to give you the real number of tables this query spans

 

[nevets72]

BP, I'm confused as to why you'd want me to remove the Union section....I did that, and the report runs fine, but I need the info from the Rpt_Actuals_MA view, hence the Union. Maybe I'm misubderstanding what you're saying? Thanks for the reply though!

The Rpt_Actuals_MA view utilizes 11 tables....10 of which are the same tables used in the first part of the query. I guess SQL Server considers this to be 21 tables, even though it really is just 11 unique tables. Anyway, I don't think I have a solution to this problem. I ran the exact same query in 7.0 and it worked fine.....I guess 6.5 has a limitation of 16 tables.

 

[arrowhouse]

If dbo.Rpt_Actuals_MA is a view with 10 tables
then you have

1) 10
2) 1
3) 9

ie 20 tables in the query & you're stuffed in 6.5

What is the diffence between dbo.Rpt_Actuals_MA & the view you are creating? Could you alter the view you are creating so that you can get all the information you need without doing a union on the dbo.Rpt_Actuals_MA view.

If its ok for you to use 7.0 that's the easiest bet.

 

[bperry]

arrowhouse:

>>....requested you may well break the magic 16.
Are you thinking that the 16 is related to CR (I am), only because I'm aware of no such limitation within SQL Server. (In fact, I think you can probably put 256 tables into a Select.)


nevets72:

>>BP, I'm confused as to why you'd
>>want me to remove the Union section
Yes, I understand that you need it. I suggested removing it (I guess I should have said 'temporarily') to see if it was that particular thing that was making CR throw up.

>>....I did that, and the report runs fine...
Okay, so now you know than CR doesn't like the Union, and that your query is otherwise okay (which you already pretty much knew because it ran in QA)

So the question becomes, 'Why doesn't CR like a Union query?' Which someone in the CR forum probably knows the anwser to. (i.e. you are probably not the first one to run into this problem)

Hope you're getting closer,
bp

 

[MalcolmW]

The SQL statement that Crystal Reports uses must begin with SELECT. So just drop that CREATE VIEW part off.;-) Next, CR doesn't like you messing around with Grouping, so you need to can that also. Lastly, when doing UNIONS, the order of the fields is important, so a full select might be giving you some datatype mismatches.

You would probably be much happier if you did this either in a view or stored proc that you then point CR at.


Malcolm Wynden
Authorized Crystal Engineer

http://www.wynden.net

malcolm@wynden.net

 

[arrowhouse]

>>bperry

in MSSQL Server 6.5 16 table query was the limit. Life is much easier in 7.0, 2000.... so no I did't think it related to CR.

Removing the union works because you then remove the 11 tables that the VIEW dbo.Rpt_Actuals_MA consists of & brings it below the 16 table limit for 6.5.

 

[bperry]

Okay, I was not aware of that re: 6.5
(Live and learn.)

thanx,
bp

 

[arrowhouse]

Certainly do - but try to avoid the hard way. Which makes groups like this (& your useful contributions) so valuable.

 

[nevets72]

You guys are awesome....thanks for all your help.....I ended up throwing all the data I needed into a table, then executed a view out of Crystal Reports which read the table.....kind of like what MalcolmW suggested.....and it worked, so thanks again for all of your suggestions!