April 26, 2010 at 2:54 pm
I'm still following this thread, but I have yet to figure out what it is you are trying to accomplish. Care to explain and show us what should be the final result? It will require that you provide DDL (CREATE TABLE stateements) for the table(s) involved, sample data (as INSERT INTO statements) for the table(s) involved, and expected results.
April 26, 2010 at 6:26 pm
we don't have primary key and foreign key relation ship on this database.
Create Table dbo.tOpp
(
OppID int,
editUserID int,
customerid int ,
partid int
)
insert into dbo.tOpp (oppid,edituserid,customerid,partid) Values(1,2,a01,Swjji2123)
insert into dbo.tOpp (oppid,edituserid,customerid,partid) Values(2,3,a02,Swjj456)
insert into dbo.tOpp (oppid,edituserid,customerid,partid) Values(3,4,a03,Swjj789)
insert into dbo.tOpp (oppid,edituserid,customerid,partid) Values4,5,a04,Swjji654)
insert into dbo.tOpp (oppid,edituserid,customerid,partid) Values(5,6,a05,Swjji987)
insert into dbo.tOpp (oppid,edituserid,customerid,partid) Values(6,7,a06,Swjji864)
Create Table dbo.tcustomer(
customerid int ,
customername varchar(20),
)
insert into dbo.tcustomer (customerid,customername) Values(1,'abc')
insert into dbo.tcustomer (customerid,customername) Values(2,'dfg')
insert into dbo.tcustomer (customerid,customername) Values(3,'ghi')
Create Table dbo.tpart(
partid int ,
partname varchar(20),
)
insert into dbo.tpart (partid,partname) Values(1,'apple')
insert into dbo.tpart (partid,partname) Values(2,'motorola')
Create Table dbo.tuser(
userd int ,
username varchar(20),
)
insert into dbo.tuser(userid,username) Values(1,'bob')
insert into dbo.tpart (userid,username) Values(2,'lolla')
I am trying to create a view which will be used for reports.and the variables i have used are from the date view .as you can see there are no reference columns .other than the columns @fmint,@fyint there are other column like customername,partname i am pulling ,i didn't mention other columns in my code.
I have changed my code as grant suggested and it worked and there is nothing problem with my view.
But, i want to update the column CST in vGPH by joining with the table in different database.i want to update CST column with 'Y' which is yes for dbo.topp.flag=1,but i am getting error.
Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'dbo.vGmopph' failed because it contains a derived or constant field.
Create Table database.dbo.tOpp
(
oppidint,
flag int NULL
)
insert into dbo.tOpp (oppID, flag) Values(1, 1)
insert into dbo.tOpp (oppID, flag) Values(2, 1)
insert into dbo.tOpp (oppID, flag) Values(3, 0)
insert into dbo.tOpp (oppID, flag) Values(4, 1)
insert into dbo.tOpp (oppID, flag) Values(5, 1)
insert into dbo.tOpp (oppID, flag) Values(6, 0)
UPDATE
dbo.vGph
SET
dbo.vGph.cst= 'Y'
FROM
dbo.vGph O
join dbo.tOpp OP
on
O.oppID= OP.oppID AND
OP.Flag=1
i just want to know if there is any way i can update the column cst in vGph ..
Thank You
April 26, 2010 at 7:34 pm
The problem you're experiencing is very straight forward. Look at the documentation from BOL that I provided:
The columns that are being modified in the view must reference the underlying data in the table columns directly. They cannot be derived in any other way...
You can't update values in derived columns. Your update statement can't include that column, or the two aliases as columns. Those are all derived columns. Instead, you can create standard stored procedures that perform updates.
I'm still confused by why you need to dynamically create views for use with your data. It's an odd approach, so I don't get what business need you're hoping to fulfill.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 26, 2010 at 11:08 pm
Based on the tables and the sample data, what is the expected output? Show us, don't describe it. We need to know precisely what you are expecting so that we can test our solutions. At this time, I still don't know what you are attempting to do.
April 27, 2010 at 8:45 am
Grant Fritchey (4/26/2010)
I'm still confused by why you need to dynamically create views for use with your data. It's an odd approach, so I don't get what business need you're hoping to fulfill.
We have a database called ABCD and tables in that database are created and loaded by other person,Now my superior wants to clean that database and delete it permenantely since all the table names and column names in ABCD Database are not good and part of the data is no longer in use, there are many reports which are using ABCD Database .Now, my superior wants me to create views in different database with same names initially for all the tables in the database (ABCD)and connect the reports to the views if everything works good he wants to take backup of ABCD database and delete it.
In this process, there is a table called GPH in ABCD ,where he used variables and update statement to load the data into the table.
I haven't read the documentation you mentioned yet.
I think i can write a case statement for CST Column i will try that and let you know.
Is it a good decision to create views for all the tables in ABCD database?
I have attached my expected results ,you can see the column CST.
April 27, 2010 at 9:13 am
itskumar2004 (4/27/2010)
Grant Fritchey (4/26/2010)
I'm still confused by why you need to dynamically create views for use with your data. It's an odd approach, so I don't get what business need you're hoping to fulfill.We have a database called ABCD and tables in that database are created and loaded by other person,Now my superior wants to clean that database and delete it permenantely since all the table names and column names in ABCD Database are not good and part of the data is no longer in use, there are many reports which are using ABCD Database .Now, my superior wants me to create views in different database with same names initially for all the tables in the database (ABCD)and connect the reports to the views if everything works good he wants to take backup of ABCD database and delete it.
In this process, there is a table called GPH in ABCD ,where he used variables and update statement to load the data into the table.
I haven't read the documentation you mentioned yet.
I think i can write a case statement for CST Column i will try that and let you know.
Is it a good decision to create views for all the tables in ABCD database?
I have attached my expected results ,you can see the column CST.
That makes some sense. I'm still unsure why you're creating views on the fly like that though. However, by & large, setting up a series of views that look the same is the easiest way to fix the reporting issue. But they won't work for updates. You simply can't derive columns in a view AND use those columns when doing updates, whether you use CASE or not.
Derived columns <> Updatable Views
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 27, 2010 at 9:37 am
I meant like this not with the update statement:
EXEC('
CREATE VIEW dbo.vGph
WITH SCHEMABINDING
AS
O.OpportunityID as Opp_Code,
''DEFAULT'' as method,
''' + @Year + ''' as FYInt,
'''+ @Month + ''' as FMInt,
Case OP.OracleFlag when 1 then ''Y'' Else Null End as Cst_From_Oracle
FROM
dbo.Opp O
join phxSNAP.dbo.tOpportunity OP
on O.OpportunityID= OP.OpportunityID AND
OP.OracleFlag=1
inner join dbo.tCustomer C
on O.CustomerID = C.CustomerID
INNER JOIN dbo.tPart P ON P.PartID = O.PartID
LEFT OUTER JOIN dbo.tUser U ON U.UserID = O.EditUserID
')
I executed it ,and it worked .
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply