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 allopw 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?
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?