Updateable View

[Eric Marthinsen]

Hello-

I have a view that used to be updateable (except for a few fields, but that is fine). When I use the results of a CASE statement as one of the field values, the entire view is not updateable. The view is pasted below. Does anyone know how to keep my view updateable and, somehow, add this new field? Thank you.

-Eric


SELECT
DATEPART(yy, Orders.NextShipDate) AS year,
DATEPART(mm, Orders.NextShipDate) AS month,
DATEPART(dd, Orders.NextShipDate) AS day,
People.LastName,
People.FirstName,
Customers.CustomerID,
Orders.NextShipDate,
Customers.Custom,
Orders.AllocationTemp,
Orders.ShippingTemp,
Orders.HorseName,
Orders.PriorShipDate,
Customers.Username,
Customers.Password,
Orders.OrderID,
Orders.Deleted,
Orders.Deadbeat
FROM
People
INNER JOIN Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ON People.PeopleID = Customers.PeopleID
WHERE
(CONVERT(DATETIME, CONVERT(CHAR(10), Orders.NextShipDate, 101)) <= DATEADD(d, 7, CURRENT_TIMESTAMP)) AND (Orders.Deleted = 0) AND
(Orders.Deadbeat = 0) AND
(Customers.Deleted = 0) AND
(Customers.Pending = 0) AND
(Orders.OrderID NOT IN
(SELECT Orders.OrderID
FROM Items INNER JOIN Products ON Items.ProductID = Products.ProductID INNER JOIN Orders ON Items.OrderID = Orders.OrderID
WHERE
(Items.Dose = 0) AND
(Products.IsPharmaceutical = 1) AND
(CONVERT(DATETIME, CONVERT(CHAR(10), Items.Created, 101)) > DATEADD(d, - 5, CURRENT_TIMESTAMP)) AND
(Items.Deleted = 0))) AND
EXISTS (SELECT ItemID FROM Items WHERE Items.OrderID = Orders.OrderID AND Deleted = 0)

[Eric Marthinsen]

Sorry, I posted the query that didn&#39;t have the case statement. Here&#39;s the actual query.

SELECT
DATEPART(yy, dbo.Orders.NextShipDate) AS year,
DATEPART(mm, dbo.Orders.NextShipDate) AS month,
DATEPART(dd, dbo.Orders.NextShipDate) AS day,
dbo.People.LastName,
dbo.People.FirstName,
dbo.Customers.CustomerID,
dbo.Orders.NextShipDate,
dbo.Customers.Custom,
dbo.Orders.AllocationTemp,
dbo.Orders.ShippingTemp,
dbo.Orders.HorseName,
dbo.Orders.PriorShipDate,
dbo.Customers.Username,
dbo.Customers.Password,
dbo.Orders.OrderID,
dbo.Orders.Deleted,
dbo.Orders.Deadbeat,
CASE
WHEN (EXISTS (SELECT
Items.ItemID
FROM Items, Products
WHERE
Items.ProductID = Products.ProductID AND
Items.Deleted = 0 AND
Products.IsPharmaceutical = 1 AND
Orders.OrderID = Items.ItemID))
THEN 1
ELSE 0
END AS HasPharma
FROM dbo.People INNER JOIN
dbo.Customers INNER JOIN
dbo.Orders ON dbo.Customers.CustomerID = dbo.Orders.CustomerID ON dbo.People.PeopleID = dbo.Customers.PeopleID
WHERE (CONVERT(DATETIME, CONVERT(CHAR(10), dbo.Orders.NextShipDate, 101)) <= DATEADD(d, 7, CURRENT_TIMESTAMP)) AND (dbo.Orders.Deleted = 0)
AND (dbo.Orders.Deadbeat = 0) AND (dbo.Customers.Deleted = 0) AND (dbo.Customers.Pending = 0) AND (dbo.Orders.OrderID NOT IN
(SELECT Orders.OrderID
FROM Items INNER JOIN
Products ON Items.ProductID = Products.ProductID INNER JOIN
Orders ON Items.OrderID = Orders.OrderID
WHERE (Items.Dose = 0) AND (Products.IsPharmaceutical = 1) AND (CONVERT(DATETIME, CONVERT(CHAR(10), Items.Created, 101)) > DATEADD(d,
- 5, CURRENT_TIMESTAMP)) AND (Items.Deleted = 0))) AND EXISTS
(SELECT ItemID
FROM Items
WHERE Items.OrderID = Orders.OrderID AND Deleted = 0)