Problem with query on a view created by a union

[Carlo]

Hi,

I have a view V1 created as a plain UNION from 2 tables:

SELECT * FROM T1
UNION
SELECT * FROM T2

If I create another view V2 just filtering V1 through a WHERE clause (e.g. SELECT * FROM V1 WHERE x < y) I don't get any rows returned.
However, if I write the SELECT clause within each branch of the former UNION, I correctly get all the rows.

Is this a known bug of SQL Server 2000? If yes, is there a SP fixing it?

thx,
smiley61

[Kevin3NF]

Hello Carlo/Smiley61,

Can you please post the current SP level you are on?

If possible, please also post the CREATE TABLE statements and some inserts to populate them.

Lastly, ifyou post the query that does work, that will help give a basis for comparison.

To answer the direct question, I am not aware of any issues surrounding this but will be happy to look.

Thanks,

Kevin3NF

[Carlo]

I apologize for forgetting version details.

The problem has occurred on SQL Server 2000 Standard Edition SP4 (build 8.00.2039).

Actually we have noticed something else, in the meanwhile, that may be useful.

The problem happens when the UNION is between 2 tables with a very large number of columns.
After that we have seen that at a certain point SQL Server shifts the content of the columns by 1. That means that the column labeled 56 still exists but its content is now under column 57, and so on.
Which explain why the successive query does not return any result.

thx again for helping,
Carlo

[Kevin3NF]

A follow-up...

Are you actually using Select * From...or was that just for example here?

I'd love to know more about what this means:
"After that we have seen that at a certain point SQL Server shifts the content of the columns by 1. That means that the column labeled 56 still exists but its content is now under column 57, and so on"

If you have the time...

Thanks,
Kevin3NF

[COBOLdinosaur]

I'd like to hear about that as well.  It sounds like really strange behavour.  :scratch:

[NeoTeq]

No, it's behavior that sounds familiar to me too. I've had similar problems when creating views where I did a SELECT * FROM, whether in UNION or not. So, it would be very interesting to know if the problem still exists when selecting specific columns. This method always solved the problem on my end.

I'm also on SQL Server 2000 SP 4.