256 tables in views - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

256 tables in views

Author

Topic

CDBanks
Starting Member

23 Posts

Posted - 2005-08-30 : 16:39:39

I have a SQL script that I attempted to write into a view. If I run the script in SQL Query Analyzer it works perfectly. When I attempt to run execute as a view I receive the following error message: "[MS][ODBC SQL Server Driver][SQL Server] The query and the views or function in it exceeded the limit of 256 tables."

My objective is to use the recordset from the view to create a crystal report but have been unable to replicate the "query" effect directly in crystal. (unfortunately I am also a new crystal user)

All my searches have been fruitless. I remember seeing something about SQL Server 2000 supporting 1000 tables and older versions supporting 256 in an Access forum but have not been able to find that thread a second time.

Any provided solution would be greatly appreciated. (I am getting tired of hearing the "gears" in my head grinding. Is smoke from the ears normal?)

SELECT SubID, SubMenuDesc, ItemID, ItemDesc, 0 AS ModID, 'n/a' AS ModDesc, round(ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm
WHERE (ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, round(vwModifiersNorm.ItemPrice/100,2) as ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice = 0) AND (vwModifiersNorm.ItemPrice <> 0)

UNION
SELECT vwSubMenuNorm.SubID, vwSubMenuNorm.SubMenuDesc, vwSubMenuNorm.ItemID, vwSubMenuNorm.ItemDesc, vwModifiersNorm.ItemID AS ModID,
vwModifiersNorm.ItemDesc AS ModDesc, ROUND((vwSubMenuNorm.ItemPrice + vwModifiersNorm.ItemPrice)/100, 2) AS ItemPrice
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID
WHERE (vwSubMenuNorm.ItemPrice <> 0) AND (vwModifiersNorm.ItemPrice <> 0)

ajthepoolman
Constraint Violating Yak Guru

384 Posts

Posted - 2005-08-30 : 16:50:54

What about creating this as a stored procedure instead of a view. Crystal can use a stored procedure to obtain a recordset, so this might be a viable solution.

Aj

Hey, it compiles.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-30 : 16:54:20

I would guess that the error is because when you add all the joins in all the views that your new view is comprised of (and all the views that your sub-views are comprised of, etc) you exceed the sql limit of number of tables in a view. Do you have a whole poop-load of tables in your sub-views? Don't forget to look all the way down to the base table level for all the views' views.

Be One with the Optimizer
TG

CDBanks
Starting Member

23 Posts

Posted - 2005-08-30 : 18:02:40

No actually I have the three views that are referenced and each of those views are based upon 1 table each. So at the most the total considered would be 9.

The three views that this view relate to are necessary because the application that I am pulling the data from use a flat-file format that is not normalized. The first 3 views normalize each of the underlying tables and the final view creates my recordset.
I hope I am explaining this properly.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-30 : 19:08:56

>>based upon 1 table each
Is the table self joined a bunch of times to achieve this normalization?

>>when you add all the joins in all the views
because even 1 table self joined 3 times is considered 3 tables toward that 256 limit.

Otherwise that's a strange error to get. If you don't have a bunch of self joins maybe you could post the view definition for one of the more complex examples

Be One with the Optimizer
TG

CDBanks
Starting Member

23 Posts

Posted - 2005-08-31 : 11:03:23

The 3 views that are the source for the 4th view are normalized with through UNION. Below is a sample of the simplist first view.

CREATE VIEW dbo.vwItemNorm
AS

SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD1
FROM ITM
WHERE ITM.MOD1 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD2
FROM ITM
WHERE ITM.MOD2 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD3
FROM ITM
WHERE ITM.MOD3 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD4
FROM ITM
WHERE ITM.MOD4 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD5
FROM ITM
WHERE ITM.MOD5 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD6
FROM ITM
WHERE ITM.MOD6 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD7
FROM ITM
WHERE ITM.MOD7 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD8
FROM ITM
WHERE ITM.MOD8 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD9
FROM ITM
WHERE ITM.MOD9 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD10
FROM ITM
WHERE ITM.MOD10 <> 0

So If I am reading your post correctly that would be 10 tables?
1 view uses 10, 54 for another, and 48 for the 3rd. Thats still away from 256. Thanks for the idea. Perhaps because I am using UNION as my operator there is some sort of multiplication taking place?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-08-31 : 11:13:21

lets see...10+48+54 = 112 tables.

Then your master view you are trying to create:


--48 tables 
FROM vwSubMenuNorm
WHERE (ItemPrice <> 0)

UNION --+ 112 tables
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID

UNION -- + 112
FROM vwSubMenuNorm INNER JOIN
vwItemNorm ON vwSubMenuNorm.ItemID = vwItemNorm.ID INNER JOIN
vwModifiersNorm ON vwItemNorm.MOD1 = vwModifiersNorm.ModID

48+112+112 = 272 tables.
I think we may have found the limit buster

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-08-31 : 11:44:24

quote:
Originally posted by CDBanks

The 3 views that are the source for the 4th view are normalized with through UNION. Below is a sample of the simplist first view.

CREATE VIEW dbo.vwItemNorm
AS

SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD1
FROM ITM
WHERE ITM.MOD1 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD2
FROM ITM
WHERE ITM.MOD2 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD3
FROM ITM
WHERE ITM.MOD3 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD4
FROM ITM
WHERE ITM.MOD4 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD5
FROM ITM
WHERE ITM.MOD5 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD6
FROM ITM
WHERE ITM.MOD6 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD7
FROM ITM
WHERE ITM.MOD7 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD8
FROM ITM
WHERE ITM.MOD8 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD9
FROM ITM
WHERE ITM.MOD9 <> 0

UNION
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
ITM.MOD10
FROM ITM
WHERE ITM.MOD10 <> 0

So If I am reading your post correctly that would be 10 tables?
1 view uses 10, 54 for another, and 48 for the 3rd. Thats still away from 256. Thanks for the idea. Perhaps because I am using UNION as my operator there is some sort of multiplication taking place?

Always use UNION ALL unless you need the behavoir of having the UNION remove duplicates; it is much, much more efficient.

In this case, if you can create another table on the server, you can use only TWO tables and use a CROSS JOIN. To normalize the 10 "mod" columns, if you have a table of numbers from 1-10, you can do this:


SELECT A.* FROM
(
  SELECT ITM.ID,
	 ITM.LongName,
	 ITM.Priority,
         case when N.Number = 1 then MOD1
             when N.Number = 2 then MOD2
             when N.Number = 3 then MOD3
             ...
             when N.Number = 10 then MOD10
         end as Mod
  FROM 	
         ITM
  CROSS JOIN
         Numbers N
  WHERE
         N.Number between 1 and 10
) A
WHERE Mod <> 0

I have used the derived table to allow you to use a simple WHERE clause to exclude "Mod" values of 0 w/o needing to repeat the entire CASE expression.

The cross join has the effect of looping through your ITM table 10 times, and each time through the CASE expression pulls out the value from a different column, effectively normalizing your data and acting basically the same as 10 UNIONs.

CDBanks
Starting Member

23 Posts

Posted - 2005-09-01 : 11:25:05

There is a reason they call this "new to SQL Server" so please understand this is a question based upon my stupidity.

a) In the example you show will it still act as if there are 10 tables? (effectively resulting in the same error of >256 tables. That's what you referred to as looping though)
b) In your example where are you declaring and the value from N.Number? When I run this I get an "Invalid object name ' numbers'."

Sorry for my ignorance, and thank you for your help!

SELECT A.* FROM
(
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
case when N.Number = 1 then MOD1
when N.Number = 2 then MOD2
when N.Number = 3 then MOD3
...
when N.Number = 10 then MOD10
end as Mod
FROM
ITM
CROSS JOIN
Numbers N
WHERE
N.Number between 1 and 10
) A
WHERE Mod <> 0

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-01 : 11:37:49

SELECT A.* FROM
(
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
case when N.Number = 1 then MOD1
when N.Number = 2 then MOD2
when N.Number = 3 then MOD3
...
when N.Number = 10 then MOD10
end as Mod
FROM
ITM
CROSS JOIN
(select Number = 1 union all select 2 union all select 3 ... union all select 10) N
WHERE
N.Number between 1 and 10
) A
WHERE Mod <> 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-01 : 11:41:08

then you can replace the derived table by

SELECT A.* FROM
(
SELECT ITM.ID,
ITM.LongName,
ITM.Priority,
case when N.Number = 1 then MOD1
when N.Number = 2 then MOD2
when N.Number = 3 then MOD3
...
when N.Number = 10 then MOD10
end as Mod
FROM
ITM
CROSS JOIN
(select Number = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6
) N
WHERE
N.Number between 1 and 10
) A
WHERE Mod <> 0

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-01 : 11:51:01

lol
Your confusion is understandable. jsmith8858 (Jeff) threw some concepts at you that most new-comers won't have a clue about. (They don't call him Dr. Cross Join for nothin')
And nr's sql-tudiness is so far beyond normal mortals, he forgets where people are starting from :)

>>When I run this I get an "Invalid object name ' numbers'."
Jeff's solution requires an existing table called "numbers".
ie: create table Numbers (number int)
This table can be a handy tool when combined with queries that perform cross joins. A cross join will generate an intentional cartisean product. ie: 2 tables, 10 rows each, cross joined will produce 100 rows. For your query to work you would need to create that table and add the values 1 through 10. You would also need to complete the remaining case statements (3-9)

nr's solution just replaces an existing numbers table with a "derived" table.

>>will it still act as if there are 10 tables
No. That query will count as 2 tables

Be One with the Optimizer
TG

CDBanks
Starting Member

23 Posts

Posted - 2005-09-01 : 12:57:57

You guys are the best, especially if this will help me reduce the 256 tables!

I think I understand how that is working for the ITM table. I have two other tables that have similar normalization problems. The kink in the problem is they have more than 1 item that needs to be normalized. Basically I am attempting to pull:

SELECT A.* FROM
(
SELECT Mod.ID AS ModID,
Mod.LongName as ModDesc,
case WHEN N.Number = 1 THEN Item01
WHEN N.Number = 2 THEN Item02
WHEN N.Number = 3 THEN Item03
WHEN N.Number = 4 THEN Item04
END AS Item,
CASE WHEN N.Number = 1 Then Price01
WHEN N.NUMBER = 2 THEN Price02
WHEN N.Number = 3 THEN Price03
WHEN N.Number = 4 Then Price04
End As Price
FROM
MOD
CROSS JOIN
(SELECT Number = 1 UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4) N
WHERE
N.Number BETWEEN 1 AND 4
) A

Thanks again for all you help.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-01 : 14:41:09

You don't need the WHERE for the Number table if you are generating it using SELECT statements, as in your last post.

Sorry about the confusion, I kind of mentioned that you'd need to create a Numbers table but I was definitely very vague.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources