SQLTeam.com | Weblogs | Forums

How to change the data type in a view


#1

hello

i have the following view in sql server and i want to change the data type of runout to numeric(18,2)
but it doesn't work
can you say me please how to do this?
thankl you

ALTER VIEW [dbo].[View_3]
AS
SELECT dbo.v_numberofpnverbrauch.numberofpnforverbrauch, dbo.[v_numberofpnfordiff<0].numberofpn AS [fordiff<0],
dbo.v_numberofpnforeachdisponent.numberofpn AS foreachfehlteil, dbo.v_numberofpnverbrauch.Disponent,
dbo.v_numberofpnforeachdisponent.Fehlteil_Code_Bezeichnung,
cast(dbo.[v_numberofpnfordiff<0].numberofpn / dbo.v_numberofpnverbrauch.numberofpnforverbrauch AS runout as numeric(18,2))
FROM dbo.[v_numberofpnfordiff<0] INNER JOIN
dbo.v_numberofpnverbrauch ON dbo.[v_numberofpnfordiff<0].Disponent = dbo.v_numberofpnverbrauch.Disponent INNER JOIN
dbo.v_numberofpnforeachdisponent ON dbo.v_numberofpnverbrauch.Disponent = dbo.v_numberofpnforeachdisponent.Disponent
GROUP BY dbo.v_numberofpnverbrauch.numberofpnforverbrauch, dbo.[v_numberofpnfordiff<0].numberofpn, dbo.v_numberofpnforeachdisponent.numberofpn,
dbo.v_numberofpnverbrauch.Disponent, dbo.v_numberofpnforeachdisponent.Fehlteil_Code_Bezeichnung

GO


#2
cast(dbo.[v_numberofpnfordiff<0].numberofpn / dbo.v_numberofpnverbrauch.numberofpnforverbrauch as numeric(18,2)) AS runout

#3

thank you
i have (dbo.[v_numberofpnfordiff<0].numberofpn as 1502 and dbo.v_numberofpnverbrauch.numberofpnforverbrauch as 1059
the division is 0.99 but it shows me 0
do you know what is my fault?
which data type should i consider here?
thank you


#4

when integer value divide by another integer you will get the result in integer. So 1502 / 1059 = 0

to get decimal, convert one of the column to decimal

convert(decimal(18,2), dbo.[v_numberofpnfordiff<0].numberofpn) / dbo.v_numberofpnverbrauch.numberofpnforverbrauch

or simply multiply by 1.0

dbo.[v_numberofpnfordiff<0].numberofpn / dbo.v_numberofpnverbrauch.numberofpnforverbrauch * 1.0

#5

Yes, multiply by 1.0, but leave the CAST in to reduce the number of decimal places in the final result:

cast(dbo.[v_numberofpnfordiff<0].numberofpn * 1.0 / dbo.v_numberofpnverbrauch.numberofpnforverbrauch AS runout as numeric(18,2))