I am having a problem with a sql view. My actual views encompass several joins, but for the purposes of my question I will demonstrate the issue with smaller examples.
Say I have the views…
create view A
as
select Id as IdC
from tableA
go
create view B
as
select b.Id,
b.Name,
a.*
from tableB b
inner join A a on a.Id = b.Id
go
So all is well. Then I change view A to read…
alter view A
as
select Id as IdColumn
from tableA
go
So now if I write…
select * from A
It returns column IdColumn
However, if I write…
select * from B
It returns the original IdC
column name from view A
I tried sp_refreshview
, but that has not helped.
How can I get view B to return the updated column name from view A?
UPDATE **
Well I messed up the original question. I thank everyone for their responses. I intend to join view A to table B in view B. It seems the alter statement on view B solves the issue.
As I can see you query you are refering tableA not view A
select b.Id,
b.Name,
a.*
from tableB b
inner join tableA a on a.Id = b.Id
So modify the above query will resolve you issue
Modified query for view B
select b.Id,
b.Name,
a.*
from tableB b
inner join A a on a.IdColumn = b.Id
Your viewB is joining tableA, not view A, try:
inner join A a on a.Id = b.Id
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With