View in SQL 7 doesn't work in upgrade from 6.5

[nuVBer]

I had this view in version 6.5 but during the conversion to 7.0 it disappeared and now it has errors and won't allow me to save it to 7.0:


if exists (select name from sysobjects where name = 'ess_OverridesCombined_vw')
drop view ess_OverridesCombined_vw
go

create view ess_OverridesCombined_vw as
/* Just the intersection of Oil and Gas */
select a.comp_sk, a.year, a.month, a.oil, b.gas, a.comment

from ess_overrideoil_vw a
inner join ess_overridegas_vw b

on a.comp_sk = b.comp_sk and a.year = b.year and a.month = b.month

UNION

select a.comp_sk, a.year, a.month, a.oil, b.gas, a.comment

from ess_overrideoil_vw a
Left join ess_overridegas_vw b

on a.comp_sk = b.comp_sk and a.year = b.year and a.month = b.month
where b.comp_sk is null

UNION

select b.comp_sk, b.year, b.month, a.oil, b.gas, b.comment

from ess_overridegas_vw b
Left join ess_overrideoil_vw a
on a.comp_sk = b.comp_sk and a.year = b.year and a.month = b.month
where a.comp_sk is null
go
grant all on ess_OverridesCombined_vw to public
go


What is wrong with it?

 

[dbmsguy]

i do not beleive you are allowed to do a union in a view...... Paul

http://www.dbmsguy.com

 

[nuVBer]

So it was allowable in version 6.5, but not in 7.0?

 

[redlam]

actually, i don't see anything wrong with it. you should be able to create the same view in sql7. what sort of error are you getting? can you paste the error text?

 

[nuVBer]

The first problem I get is "Drop View is not supported.

 

[redlam]

huh. i did my best to recreate your base tables on my own server... i copied and pasted your code from above and executed it without any errors. sorry i can't be of more help :-( but i would suspect that the problem is outside of your create view code.

 

[tlbroadbent]

Assuming you are using Enterprise Manager do the following.

Remove the first five lines at the top of your view. They are not needed. Remove the Grant statement at the end and run it later from Query Analyzer, after the view is created.

Then click OK to apply the changes. If additional errors occur that you can't resolve, let us know. Terry Broadbent
Please review faq183-874.

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin