sql server mseq: update in view -- sql server mseq

update in view

CyberFox
10/21/2005 3:15:02 AM

sql server mseq: I have the following view in my db:

SELECT dbo.vw_rptShortagesFG.SalesOrder,
dbo.vw_rptShortagesFG.ReqShipDate, dbo.vw_rptShortagesFG.MStockCode,
dbo.vw_rptShortagesFG.Style,
dbo.vw_rptShortagesFG.ColourCode,
dbo.vw_rptShortagesFG.Colour, dbo.vw_rptShortagesFG.[Size],
dbo.vw_rptShortagesFG.Units,
dbo.vw_rptShortagesFG.UnitsOnHand,
dbo.vw_rptShortagesFG.UnitsShortage, dbo.vw_rptShortagesFG.PairsShortage,
dbo.vw_rptShortagesFG.MStockCodeL2,
dbo.InvWarehouse.QtyOnHand AS L2PairsOnHand,
(dbo.InvWarehouse.QtyOnHand -
dbo.vw_rptShortagesFG.PairsShortage) * - 1 AS L2PairsShortage
FROM dbo.vw_rptShortagesFG LEFT OUTER JOIN
dbo.InvWarehouse ON dbo.vw_rptShortagesFG.MStockCodeL2
= dbo.InvWarehouse.StockCode
WHERE (dbo.InvWarehouse.QtyOnHand - dbo.vw_rptShortagesFG.PairsShortage
< 0)

I would like to know: Is it possible that at runtime of this view, I can
change the value of dbo.vw_rptShortagesFG.[Size]? When it is equal to 11 it
must change to 12, and when it is equal to 13 it must change to 14.

Thx in advance...

Re: update in view

Hugo Kornelis
10/23/2005 10:58:13 PM

[quoted text, click to view]

Hi CyberFox,

I'm not entirely sure what you want.

If you want to change the data in the tables each time someone queries
the view, then the short answer is that this is impossible. Look into
stored procedures instead.

If you want to know if you can modify the data shown in a view and
expect the change to make it through to the underlying base tables, then
the answer is "it depends". The exact requirements for views to be
updateable are in Books Online. Off the top of my head, I'd wager that
dbo.vw_rptShortagesFG.[Size] is updateable in the above view, but don't
bet on it without checking and double-checking first.

If you want to display data that is different from the actual data in
the base tables without changing the actual values in the base tables,
then the answer is to use CASE:

SELECT blah, blah, blah,
CASE WHEN dbo.vw_rptShortagesFG.[Size] = 11 THEN 12
WHEN dbo.vw_rptShortagesFG.[Size] = 13 THEN 14
ELSE dbo.vw_rptShortagesFG.[Size]
END AS ModifiedSize,
blah, blah
FROM .....

Best, Hugo
--