Incorrect Column Order with Union

[tkav8]

I have two views that pull two sets of data. The order of the columns in the two views are exactly the same:
View1 - Standard, WaterLevel, FixedCost

View2 - Standard, WaterLevel, FixedCost

I then run a SQL statement that creates a union:
Select * From View1
union
Select * From View2

Problem is that when I view the results the data that should be in the WaterLevel column is placed in the FixedCost column. I have run both views seperately, and they are in the correct order when I run them. In the union the data is switched between these two columns.

Any help would reduce the pain in my head.

 

[fluteplr]

you might want to force a recompile on the two views.

There is a storedproc to do this whose name I can never remember or you can just open the view in enterprise manager make a dumb change like adding a space and resave the view.

Then see if you still have the problem.

 

[SQLSister]

you will avoid problems in the future if you specify the columns in the selct statements, then even if they are in a differnt order in the table or view they will always put the right data in the right column. Using selct * is usuallly a bad practice but especially so in a union query where the number of columns must match.

 

[fluteplr]

Good point, I can't beleive I missed that.

 

[tkav8]

Tried specifying all fields and it worked perfectly. Thanks for the replies SQLSister and fluteplr. No more head pain.