Can someone tell me when I would get an error like the one below:
"Update or insert of view or function failed because it contains a derived or constant field."
Here is how I create a VIEW
CREATE VIEW vSample
AS
SELECT ilm.ITEM_ID,
CAST (ilm.CURRENT_QUANTITY AS INT) CURRENT_QUANTITY
FROM SAMPLE_1 AS ilm INNER_JOIN SAMPLE_2 AS ilm2
on ilm.ID = ilm2.ID
Now, I am trying to update data using the view
UPDATE [vSample] SET [CURRENT_QUANTITY] = 598.00 WHERE (1 = 1)
I have tried casting the number 598.00 as int and no luck.
Can someone please help or guide me on this problem?
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
Hi everyone, I have a problem like this . I have tables Coursegroupcode, which has groupname, codeI have Courses That has Coursename, its code(group code),Term, Course Number Enrollment table which has Foreign keys Term,Course NUmber , SSN I need to get a view like thisI should list all the coursecodes and people enrolled for each course code for selected terms Course Table Primary keys(TERM,COUSE Number)Enrollment Table(Foreign keys) TERM ,COURSE NUMBER, SSNplease help
Can SQL Views insert to the tables they are created from?I have a database that is not well structured. It has a lot of redundency. What I want to do is create a SQL View that brings in all the data I need and have my application use that new SQL View instead of the data tables. Then I want to be able to insert new information to a SQL View that actualy gets inserted into the tables that the SQL Viewer is created from.Can this be done?Does this make any sense?
I have a query which unions the four select statements.....
the select statements are joined with other tables and views.....
When i execute the query i get ODBC timeout error........
But the strange thing is that if i execute the view individually once and again execute the query it works fine.......and later it justs works fine....
Is there a performance hit running a sp against a view versus a base table. The view just excludes several of the records based on some criteria, and all the data I will be retreiving is included in the view. Or should I just stick my criteria in the SP to exclude the data?
I have some rather complex views to work with. Do they slow you down? Would it be better to move a view into a stored procedure? Is there any difference between these three solutions? <B>1.Using views:</b> Create view X As Select Col1, Col2, Col3 from Table1 Go
Create view Y As Select Col1, Col2, Col3 from Table2 Go
Create proc Z As Select X.Col2, X.Col3, Y.Col2, Y.Col3 From X inner join Y on X.Col1 = X.Col1 GO
<b>2.Using just a stored proc:</b> Create proc Z As Select X.Col2, X.Col3, Y.Col2, Y.Col3 from (Select Col1, Col2, Col3 from Table1) X inner join (Select Col1, Col2, Col3 from Table2) Y on X.Col1 = Y.Col1
<b>.Joining tables</b> Create proc Z As Select Table1.Col2, Table1.Col3, Table2.Col1, Table2.Col3 FromTable1 inner join Table2 on Table1.Col1 = Table2.Col1 go
My First time building "Views" in SQL...... I'm trying to figure out how to return a 1 instead of a two when I Count the number of records that return inn my view.
here's what I have;
SELECT TOP 100 PERCENT dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate, COUNT(dbo.tbl_ProcTimesheet.TransAmt) AS Shifts, dbo.tbl_ProcTimesheet.[Employee Number], dbo.tbl_ProcTimesheet.YCode, dbo.tbl_ProcTimesheet.XCode, dbo.tbl_ProcTimesheet.ZCode, dbo.tbl_ProcTimesheet.eeLink FROM dbo.tbl_ProcTimesheet INNER JOIN dbo.tbl_SysVarRestEeShiftPayCodes ON dbo.tbl_ProcTimesheet.Code = dbo.tbl_SysVarRestEeShiftPayCodes.PayCode INNER JOIN dbo.tbl_SysVarRestEeShiftRules ON dbo.tbl_ProcTimesheet.YCode = dbo.tbl_SysVarRestEeShiftRules.YCode AND dbo.tbl_ProcTimesheet.ZCode = dbo.tbl_SysVarRestEeShiftRules.ZCode WHERE (dbo.tbl_ProcTimesheet.Sequence <> N'0') AND (dbo.tbl_ProcTimesheet.Week < 3) GROUP BY dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_ProcTimesheet.TransAmt, dbo.tbl_SysVarRestEeShiftRules.DailyHours, dbo.tbl_ProcTimesheet.[Employee Number], dbo.tbl_ProcTimesheet.XCode, dbo.tbl_ProcTimesheet.YCode, dbo.tbl_ProcTimesheet.ZCode, dbo.tbl_ProcTimesheet.eeLink, dbo.tbl_ProcTimesheet.Sequence, dbo.tbl_ProcTimesheet.Week HAVING ('IIf' > '1,1,0') AND ('if' > '1,1') AND (dbo.tbl_ProcTimesheet.erNum LIKE N'5648 ') AND (SUM(dbo.tbl_ProcTimesheet.TransAmt) >= dbo.tbl_SysVarRestEeShiftRules.DailyHours) AND (dbo.tbl_ProcTimesheet.PayDate = CONVERT(DATETIME, '2005-09-06 00:00:00', 102)) AND (COUNT(dbo.tbl_ProcTimesheet.TransAmt) > 0) ORDER BY dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_ProcTimesheet.[Employee Number]
When it counts shifts, I only want to return a maximum of i, as in either you had a shoft that day, or not. I gave up trying to figure out the "IF" string in SQL, and for the life of me I can not figure this out.
Does anyone know a good site with info on how to construct an SQL View?
MS SQL Server 2005 What happens if 2 or more users use the same View at the same time or while other user is using it, normally from Crystal Reports 10 It rebuilds again? Or?
I have a SQL server: SQL2005 2 databases db_repl: contains replicated data from mainframe db_my: my database
db_my.vwRepl_WorkOrders: Select 1,2,3 from db_repl.dbo.WorkOrders db_my.vwWorkOrdersOpen: select 1,2,3 from db_my.vwRepl_WorkOrders where Status=1
which approach is more optimal to do in order to find open work orders from Texas: [select 1,2,3 from db_repl.dbo.workOrders where Status=1 and State=1 or select 1,2,3 from db_my.vwWorkOrdersOpenwhere where State=1] Is it best practice to use 'subviews' that acess db_repl directly or acess views in my_db that acess db_repl (2 hops or more if views call other views that call other views) Answer might be the obvious but just in case I am missing something here . Thanks!
I've created views ProductsSold that will list all the products- ProductID and quantity, that were ordered (OrderDate) in last twenty four hours between getdate()-1 and getdate().
create view ProductsSold as select orderdetails.ProductID, orderdetails.OrderQty, orders.OrderDate from OrderDetails inner join Orders on OrderDetails.salesOrderID = Orders.salesOrderID where OrderDate > dateadd(d, -1, getdate()) and OrderDate < getdate()
i just wonder if there is a more simpler way to query it?
Hi all,We have a big table connected to a web server, and I want the number ofrows to be limited that get returned.So I could do something like this:select top 10 *from objectorder by codeI then realised looking at the help file, because the "order by" clauseis set the SQL Server has to build the complete dataset, do the orderby and then filter it to the first ten rows.So I was wondering if I put the query into a view like so :create view vw_object asselect top 10 *from objectThen ran the query like so:select *from vw_objectorder by codewould the SQL server just get the top 10 rows from the view first, thenapply other order by on it afterwards?
I've searched for information on this but so far have not been able tofind any advice.We have several databases running on SQL Server 7.0 that areessentially identical in structure but are used for different data.There are several tables that are identical in all databases.Currently, we make updates to those tables in one database andpropogate the new versions of those tables to the other databases.The maintenance to keep after this is somewhat problematic in that theuser, or dba, has to initiate some action to apply the update to alltables anytime a change is made. An idea I have to simplify thisprocess is create views that access the data in a central location.I would create a database for these shared tables, and replace thetables in each individual database with a view of the same name. Theother nice thing about this solution is it is transparent toapplications that rely on this data. I contemplated using the shareddatabase by making code changes to change all the references to thosetables to DB..table, but that would take considerable man hours. Cananyone give me a reason why this would not be a good idea? Thanks.
I am supposing that SQL Mobile 2005 does not support views in its database? It is odd that all development tools show the tab of Views, but I cannot seem to find how someone would create one there. Is there a reference someone can point me to, or just reply to the forum to set me straight.
AS Select * FROM customers_table WHERE CustomerID between 213 AND 3443
Each time you want to select from the view - is the View going to perform the WHERE clause on the customers_table, or not?
Or does the view already have the virtual rows?, meaning it does not have to perform a where clause (WHERE CustomerID between 213 AND 3443) on the customers_table, and just performs an inner join with the rows it already has?
I am trying to figure out if there are any advantages to using views. I am currently creating sprocs that use select statements to display things to my app.
Should I be using views to do this? What are views intended for?
