Re: update in view

On Fri, 21 Oct 2005 03:15:02 -0700, CyberFox wrote:

>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...
>Rgds,

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
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
.