USE Northwind
GO

CREATE TABLE myTable99 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 1  AND 10), Col2 varchar(50))
CREATE TABLE myTable98 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 11 AND 20), Col2 varchar(50))
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 1, 'x' UNION ALL
SELECT 2, 'y' UNION ALL
SELECT 3, 'z'

INSERT INTO myTable98(Col1, Col2)
SELECT 11, 'x' UNION ALL
SELECT 12, 'y' UNION ALL
SELECT 13, 'z'
GO

CREATE VIEW myView99
AS
SELECT Col1, Col2 FROM myTable99
UNION ALL
SELECT Col1, Col2 FROM myTable98
GO

SELECT * FROM myView99

UPDATE myView99 SET Col2 = 'x002548' WHERE Col2 = 'z'

SELECT * FROM myView99

/*
DROP VIEW myView99
DROP TABLE myTable99, myTable98
*/

create view temp_mfg_unit_view
as

Declare @unit varchar(16)
set @unit='71054'

select 	t_cmnf, t_clot
from 	ttscfg200100 	--objects
where 	t_clot=@unit
union all 
select 	t_cmnf, t_clot
from 	terext401100	--rental contract lines
where 	t_clot=@unit

quote:

---

Originally posted by cardgunner

I'm trying to create a view but I get an error of incorrect keyword.

This is my first time creating view.

create view temp_mfg_unit_view
as

Declare @unit varchar(16)
set @unit='71054'

select 	t_cmnf, t_clot
from 	ttscfg200100 	--objects
where 	t_clot='71054'
union all 
select 	t_cmnf, t_clot
from 	terext401100	--rental contract lines
where 	t_clot='71054'

I didn't put all the tables in the code. There are about 15. As a select it works. I add the Create view... and it errors...Incorrect syntax near the keyword 'Declare'.

Can you do this type of statement when creating a view?

CardGunner

---

update tmp_mfg_unit_2 
set t_cmnf='OTH'
where t_clot='71054'

quote:

---

Originally posted by jimf

Found this trivia on the net a while ago

DECLARE @TableA TABLE (i INT)
DECLARE @TableB TABLE (i INT)

SELECT * FROM @TableA
SELECT * FROM @TableB

INSERT @TableA
OUTPUT inserted.i
INTO @TableB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3

SELECT * FROM @TableA
SELECT * FROM @TableB

---

quote:

---

Originally posted by cardgunner

I was afraid of that.

I'll have to see if '71054' can be called as a parameter in VS .net 2003. So I can create a report that asks for the unit to be updated and then the value we are updating to.

In any case I did cahnge @unit with '71054' and scratched the declare.

I created the view. Worked perfectly

Tried to update the t_cmnf and got an error. I'm trying to reasearch it now.

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

any ideas?


CardGunner

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by cardgunner

I was afraid of that.

I'll have to see if '71054' can be called as a parameter in VS .net 2003. So I can create a report that asks for the unit to be updated and then the value we are updating to.

In any case I did cahnge @unit with '71054' and scratched the declare.

I created the view. Worked perfectly

Tried to update the t_cmnf and got an error. I'm trying to reasearch it now.

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

any ideas?


CardGunner

---

One way to deal with this is to create an INSTEAD OF UPDATE trigger on view which updates the base tables instead of updating the view directly.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2669779&SiteID=1

---

quote:

---

Originally posted by X002548

You don't have to

Just update the view with a where clause

---

quote:

---

Originally posted by cardgunner

Brett,

What do you mean by predicate?

CardGunner

---

quote:

---

Originally posted by cardgunner

quote:

---

Originally posted by X002548

You don't have to

Just update the view with a where clause

---

I'm not sure what you mean.

I deleted the declare and added '71054' to parts of the union.

now the view works perfectly.

I tried to update the tables in that view and I get the error.

And there is a where in the update statement.

All the records in the view was of unit 71054 I tried the update without the where clause.

update tmp_mfg_unit_2
set t_cmnf='OTH'

and still the same error

UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.


CardGunner

---

create view tmp_mfg_unit_2
as

select 	t_cmnf, t_clot
from 	ttscfg200120 	--objects
where 	t_clot='23332'
union all 
select 	t_cmnf, t_clot
from 	terext401120	--rental contract lines
where 	t_clot='23332'

update tmp_mfg_unit_2
set t_cmnf='OTH'

Server: Msg 4416, Level 16, State 5, Line 1
UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

create view tmp_mfg_unit_2
as

select 	t_cmnf, t_clot
from 	ttscfg200120 	--objects
union all 
select 	t_cmnf, t_clot
from 	terext401120	--rental contract lines

update tmp_mfg_unit_2
set t_cmnf='OTH'
where t_clot='23332'

Server: Msg 4440, Level 16, State 9, Line 1
UNION ALL view 'tmp_mfg_unit_2' is not updatable because a primary key was not found on table '[ttscfg200120]'.