Decimal Scale -- sql server programming

Steve Kass
1/16/2004 12:59:08 PM

I don't believe this will work. A CASE expression in SQL must have a
fixed type, so despite the CAST operator, there will be an implicit
conversion to the type of the CASE expression.

SK

[quoted text, click to view]

Steve Kass
1/16/2004 1:00:52 PM

Mark,

It's a little messy, but one solution is below. You could also do it
a bit more simply (and avoid using a function) with STR, but since STR
converts to float before proceeding, it might cause rounding errors in
some rare cases:

create function titles_custom (
@d tinyint
) returns table as return
select
title_id,
case @d
when 0 then p0
when 1 then p1
when 2 then p2
when 3 then p3
when 4 then p4
when 5 then p5
when 6 then p6
else price end price
from (
select
title_id,
price,
cast(cast(price as decimal(10,0)) as sql_variant) as p0,
cast(cast(price as decimal(10,1)) as sql_variant) as p1,
cast(cast(price as decimal(10,2)) as sql_variant) as p2,
cast(cast(price as decimal(10,3)) as sql_variant) as p3,
cast(cast(price as decimal(10,4)) as sql_variant) as p4,
cast(cast(price as decimal(10,5)) as sql_variant) as p5,
cast(cast(price as decimal(10,6)) as sql_variant) as p6
from pubs..titles
) T
go

select * from titles_custom(3)

go
drop function titles_custom

SK

[quoted text, click to view]

Mark Norris
1/16/2004 2:14:38 PM

Thanks Joe.

The app that is displaying the data is not under my control - otherwise I
would handle this differently.

Mark
[quoted text, click to view]

Joe Celko
1/16/2004 4:26:55 PM

[quoted text, click to view]
variable or table column value: <<

Arrgh! Much better approach! I never think of ROUND() because it is not
Standard SQL, but every product has something like it.

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***

Steve Kass
1/16/2004 5:31:32 PM

[quoted text, click to view]
I see. That you could do with ROUND, since its second parameter can be
a variable or table column value:

create table T (
d int
)
insert into T values (5)
go

select
round(1.23465783498576,d,0), -- rounded
round(1.23465783498576,d,1) -- truncated
from T
go

drop table T

While ROUND (like many numeric features) isn't part of the standard,
it's not a completely weird proprietary thing.

SK

[quoted text, click to view]

Mark Norris
1/21/2004 12:35:53 PM

Thanks Steve
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server programming

sql server programming : Decimal Scale