Union All in a View

[KiwiGuy]

Hi All,

Im trying to create a Union All in SQL Server but not having much luck, this is basically what im trying todo.

create view aview
as

(Select * from tblA)
Union ALl
(Select * from tblb)
Union ALl
(Select * from tblc)
Union ALl
(Select * from tbld)

all the tables are identical, so its not a field conflict error, but this is the error i get

Set operators may not appear within the definition of a view.

whats wrong??

Cheers

Ben

 

[Tim1]

Ben,

I may be wrong, but I don't think that you can use a select * with a union command, I think that you will need to specify the columns in each query.

Tim

 

[KiwiGuy]

The actual query does have set column names, but thats doesnt solve my problem

 

[Tim1]

Are you sure that all table definitions are exactly the same? Even simple things like a different size field (e.g. char(30) to char(20) or a char to varchar) can make a Union query fail?

The only other thing I can offer is that I have never actually seen a Union in a view before. Which version of Sybase are you using??

Tim

 

[pishans]

Server Message: Number 197, Severity 15
Set operators may not appear within the definition of a view.

union based sets are not allowed in sybase 11.5 according to the above error msg. I don't know anything about higher version numbers.

Databases DB2 with a stronger sql syntax permits this and a lot more - like views with recursive definitions.

Søren

 

[bingbiao]

Union is only allowed in views in ASE12.5.

 

[Guest_imported]

You cannot do a UNION in a view - simple.

 

[Guest_imported]

GpotterUK is right, but ASE-12.5 allows you do this.