Using Union in a View

Using Union in a View

Post by Larry McGahe » Wed, 31 Jul 2002 04:53:03



I have 2 views that are fairly complex that select a
series of data based on particular criteria.  I want to
create a UNION view that combines this data.  The
following is the statement in this UNION View:

SELECT     FormulaId, UserType, ObjectCode,
ObjectDescription, AmountStored
FROM         dbo.vwSauceComponents
UNION
SELECT     FormulaId, UserType, ObjectCode,
ObjectDescription, AmountStored
FROM         dbo.vwPreBlendAmts

While in design mode in creating the view in SQL
Enterprise Manager, the View executes.  When I save this
view (difficulty saving) and attempt to open the view and
have rows returned, I get the following message;

"The query and the views or functions in it exceed limit
of 256 tables"

The combined views only reference 8 tables.  Many of these
tables are duplicated to allow for the drilling down into
data.

I can link these 2 views in MS Access, and create an
Access query that performs the UNION and it works fine.  I
can also perform the same function in MS Query.

Has anyone experienced a similar situation?  If so, I
would appreciate any suggestions possible.

Thanks,

Larry

 
 
 

Using Union in a View

Post by Sarah Sutterfiel » Wed, 31 Jul 2002 05:58:15


Larry,

One time, I had the same problem you are describing - I was never able to
resolve it.  In the end, I rewrote the base tables using more efficient
joining, using fewer duplicate aliases to the base tables.  Like you, it was
only a few base tables referenced, but there was many unions and subselects.
I'm interested if anyone has come up with a workaround to this problem - are
there any other solutions?


Quote:> I have 2 views that are fairly complex that select a
> series of data based on particular criteria.  I want to
> create a UNION view that combines this data.  The
> following is the statement in this UNION View:

> SELECT     FormulaId, UserType, ObjectCode,
> ObjectDescription, AmountStored
> FROM         dbo.vwSauceComponents
> UNION
> SELECT     FormulaId, UserType, ObjectCode,
> ObjectDescription, AmountStored
> FROM         dbo.vwPreBlendAmts

> While in design mode in creating the view in SQL
> Enterprise Manager, the View executes.  When I save this
> view (difficulty saving) and attempt to open the view and
> have rows returned, I get the following message;

> "The query and the views or functions in it exceed limit
> of 256 tables"

> The combined views only reference 8 tables.  Many of these
> tables are duplicated to allow for the drilling down into
> data.

> I can link these 2 views in MS Access, and create an
> Access query that performs the UNION and it works fine.  I
> can also perform the same function in MS Query.

> Has anyone experienced a similar situation?  If so, I
> would appreciate any suggestions possible.

> Thanks,

> Larry


 
 
 

Using Union in a View

Post by Minyo Mine » Wed, 31 Jul 2002 06:49:17


1. Try to execute this script you post in the Query Analyzer.
2. Try to create Stored Procedure with same sql statements and execute it.
Post results of these two -- I don't have your DDL and was unable to do it
by myself ;)
Next time post some or all DDL statements.

M.Minev


Quote:> I have 2 views that are fairly complex that select a
> series of data based on particular criteria.  I want to
> create a UNION view that combines this data.  The
> following is the statement in this UNION View:

> SELECT     FormulaId, UserType, ObjectCode,
> ObjectDescription, AmountStored
> FROM         dbo.vwSauceComponents
> UNION
> SELECT     FormulaId, UserType, ObjectCode,
> ObjectDescription, AmountStored
> FROM         dbo.vwPreBlendAmts

> While in design mode in creating the view in SQL
> Enterprise Manager, the View executes.  When I save this
> view (difficulty saving) and attempt to open the view and
> have rows returned, I get the following message;

> "The query and the views or functions in it exceed limit
> of 256 tables"

> The combined views only reference 8 tables.  Many of these
> tables are duplicated to allow for the drilling down into
> data.

> I can link these 2 views in MS Access, and create an
> Access query that performs the UNION and it works fine.  I
> can also perform the same function in MS Query.

> Has anyone experienced a similar situation?  If so, I
> would appreciate any suggestions possible.

> Thanks,

> Larry

 
 
 

1. Using UNION in a VIEW

I am getting "unpredictable" results when using two SELECTs with a UNION
ALL between them in a VIEW.  The query works correctly on its own but once
the query becomes a VIEW and then I SELECT from this VIEW I get a different
set of results.

Books On-Line (BOL) is confusing as well as MS Technet.  I find conflicting
information on VIEWs with UNION clauses.  Some of the text says that UNION
is not allowed inside a VIEW.  While other text shows examples of UNIONs in
a VIEW.  


Thanks

2. How to read the .DataSource property

3. Views using an existing Union view

4. Sybase DBA Job in the Netherlands

5. sort a union, view of union

6. BUG: "AND NOT" does not work logically with CONTAINS

7. Using UNION Views to Choose Source Database

8. newbie question

9. Using a UNION in a view

10. Using a union in VIEW BUG

11. using views with UNION

12. using union in views

13. Creating Views using a UNION query