September 1, 2009 at 3:38 am
declare @Hits decimal(6,2)
declare @RelativeImp decimal(6,2)
declare @CalculateAdminScore decimal(6,2)
set @hits = 37537
set @RelativeImp = 0.01
set @CalculateAdminScore = @Hits * @RelativeImp
print @CalculateAdminScore
Since the column length/type in table is decimal(6,2), i dont want to change the precision and scale.
So with out changing the precision and scale above, how can i make the query execute successfully?
Please help.
September 1, 2009 at 3:48 am
when you use decimal(6,2) , you are asking for 6 digits OF WHICH 2 are to the right hand side of the decimal. So you cant store 37537 in it , its to large.
http://msdn.microsoft.com/en-us/library/aa258274(SQL.80).aspx
September 1, 2009 at 5:10 am
Can't you use 'convert' while querying?
set @CalculateAdminScore = convert(decimal(10,2),@Hits) * @RelativeImp
though, it is not possible to store any big value without coverting or casting smaller datatype in to bigger one.
"Don't limit your challenges, challenge your limits"
September 1, 2009 at 9:59 am
Kruti:
CONVERT is unecessary if the target is large enough, and won't help if it isn't.
-- this will fail
declare @source int
declare @target numeric (6,2)
set @source = 35677
set @target = CONVERT(numeric(10,2),@source)
select 'Example 1:',@target
go
-- but this will succeed
declare @source int
declare @target numeric (10,2) -- NOW we have enough room
set @source = 35677
set @target = @source
select 'Example2:', @target
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 10:14 am
sudhanva (9/1/2009)
Since the column length/type in table is decimal(6,2), i dont want to change the precision and scale.
The biggest number that you can store in a decimal (6,2) is 9999.99. If you want to store bigger, you need to change the definition of the column.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 10:18 am
Good luck with your 2 a.m. presentation, Gail.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 10:26 am
Bob Hovious (9/1/2009)
Good luck with your 2 a.m. presentation, Gail.
What 2am presentation?
p.s. right thread for this question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 11:30 am
Whups
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
September 1, 2009 at 11:40 am
Bob Hovious (9/1/2009)
Whups
😀
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 1, 2009 at 10:54 pm
Bob:
You are right! Actually i had different idea in my mind. I thought the code give here was just for our reference; and the actual requirement is just this calculation
set @CalculateAdminScore = @Hits * @RelativeImp
I m completely agree with Gail that one need to change data type from (6,2) to (10,2) to get things done.
"Don't limit your challenges, challenge your limits"
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply