Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2008-12-24 : 07:29:03
|
| I have a dilema that I hope someone can help with.1. We are running MS SQL 2005.2. I have a table called progmode1.3. I have a view called djt_mt_info_to_prodtrack.4. In each table I have two similar columns, one named jobtask and the other named multiplier.5. We have an OPC server that connects the progmode1 table to PLC boxes on the production floor. This OPC server does not work with views it only works with tables.Now what I want to do is find a way to copy the multiplier column from the view to the table with the following rules:1. Where the jobtask is equal from the view and the table copy the multiplier value from the view to the table.2. Where the jobtask is not equal from the view and the table set the multiplier in the table to 1.I have successfully done this task with a view but like I said the OPC server does not like views. So what is the easiest and best way, and please because I am still fairly new to this SQL world be specific, to accomplish this goal. Thanks and have a great holiday. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-24 : 07:52:01
|
| How come view doesn't work? DO you mean this? Not sure though as your description is not clear:update tset t.multiplier = (case when t.jobtask = v.jobtask then v.multiplier else 1 end )from table t inner join view von t.ID = v.ID |
 |
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2008-12-24 : 08:56:52
|
The reason the view does not work with the OPC server is because the OPC likes to also write to the datasource connection, in this case the datasource connection is MS SQL. Since a view is exactly that, a view, that means that the OPC server can not write to it, hence the reason the OPC server does not like views.Now as for your code that is somewhat similar to what I have in my select statment that creates the view I have.What I am trying to do is idiot proof my system. I want something that runs in the backround completely un-noticed and requires no user or human intervention. So lets say I run your code here, where would I place it so that it would run all the time and keep the table updated? Is there a place where in a table design I can run this query? Like I said I am fairly new to all this, I can create tables, create views, and do some things but still have a long ways to go and a lot to learn.Thanks for the quick responce.quote:
Originally posted by sodeep
How come view doesn't work? DO you mean this? Not sure though as your description is not clear:update tset t.multiplier = (case when t.jobtask = v.jobtask then v.multiplier else 1 end )from table t inner join view von t.ID = v.ID
|
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-12-24 : 09:09:31
|
| If you know the frequency when your view refreshes, you could schedule an update statement. Or else, A trigger should help you as well. |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-12-24 : 09:21:38
|
quote:
Originally posted by mrtweaver
The reason the view does not work with the OPC server is because the OPC likes to also write to the datasource connection, in this case the datasource connection is MS SQL. Since a view is exactly that, a view, that means that the OPC server can not write to it, hence the reason the OPC server does not like views.Now as for your code that is somewhat similar to what I have in my select statment that creates the view I have.What I am trying to do is idiot proof my system. I want something that runs in the backround completely un-noticed and requires no user or human intervention. So lets say I run your code here, where would I place it so that it would run all the time and keep the table updated? Is there a place where in a table design I can run this query? Like I said I am fairly new to all this, I can create tables, create views, and do some things but still have a long ways to go and a lot to learn.Thanks for the quick responce.quote:
Originally posted by sodeep
How come view doesn't work? DO you mean this? Not sure though as your description is not clear:update tset t.multiplier = (case when t.jobtask = v.jobtask then v.multiplier else 1 end )from table t inner join view von t.ID = v.ID
Why don't you update underlying tables that are invoved in View rather than updating table from View? |
|
|
|
|
|
|
|
|
Adding column to existing table