Giant SQL Query
Posted on 2006-11-29
The powers that be demand a giant report from our database. Basically, they would like every piece of information we collect delivered in excel via a 60+ column report.
I have just moved our database from a "custom" design to a relational model. This brought the total number of tables in the DB from 10 to 32.
While working to re-create this giant report I ran into an error....
Server: Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
The reason there are so many is because each trait that is collected they want to view the raw number itself, as well as an average from a particular grouping, and the deviation of an individual from that grouping. The way I was doing this was to create a view grouped by whatever they want it deviated against and then join that view along with the table that holds that individual data to me report query. After about 52 columns I have run out of tables I can join. I guess the most straight forward answer would be to write a procedure that will insert the results into a table so I can start again from there.
In addition, they also want to view data no different from the way they used to on this particular report. While a relational design helped us to collect and determine new things (per their request) they want to view this report as if nothing changed. This makes for an ugly query.
Looking for some better ideas.... I could post the query I have so far I didn't because of the length and complexity of it, but if it would help I can post it.