# SQL View with Decimal Output

• July 18th, 11:27 AM
Heidi
SQL View with Decimal Output
Is there anyway to specify that a calculated field in a
view be displayed as a decimal?

Thank you,
Heidi
• July 18th, 11:44 AM
praveen
Re: SQL View with Decimal Output
Heidi

You need to explicitly convert the calculated field as decimal by using
convert.

for eg: select Convert(decimal(10,3),2*3) as DecimalOutput

HTH

MCSD, MCDBA

"Heidi Stoneman" <com> wrote in message
news:05d001c34d1f\$9f7bc700\$gbl...

• July 18th, 01:06 PM
Tim
Re: SQL View with Decimal Output
It would help if you posted the error. Help alot if you post create table
code & sample data.

Guessing it is divide by zero problem.

Solution add nullif value is zero.

SELECT Member, FiscalYear, SUM(CC) + SUM(SDS) + SUM
(Datanet) + SUM(XCAT) + SUM(RTWZ) AS Electric_Total,
CONVERT(decimal(10, 3), (SUM(CC) + SUM(SDS) + SUM(Datanet)
+ SUM(XCAT) + SUM(RTWZ)) /
(
NULLIF(
SUM(CC) + SUM(SDS) + SUM
(Datanet) + SUM(XCAT) + SUM(RTWZ) + SUM(ODEN) + SUM
(Telxon) + SUM(ECom)
, 0)
)
) AS Expr1 FROM dbo.MBR_OrderSummary
GROUP BY Member, FiscalYear ORDER BY Member, FiscalYear

SELECT Member, FiscalYear, SUM(CC) + SUM(SDS) + SUM
(Datanet) + SUM(XCAT) + SUM(RTWZ) AS Electric_Total,
CONVERT(decimal(10, 3), (SUM(CC) + SUM(SDS) + SUM(Datanet)
+ SUM(XCAT) + SUM(RTWZ)) / (SUM(CC) + SUM(SDS) + SUM
(Datanet) + SUM(XCAT) + SUM(RTWZ) + SUM(ODEN) + SUM
(Telxon) + SUM(ECom))) AS Expr1 FROM dbo.MBR_OrderSummary
GROUP BY Member, FiscalYear ORDER BY Member, FiscalYear

"Heidi Stoneman" <com> wrote in message
news:0d6c01c34d28\$81d0ef10\$gbl...
> decimal by using
> >
> >
> >.
> >[/ref][/ref]