January 22, 2010 at 7:18 am
I’m hoping someone can assist me with SQL Syntax. I have a view which is used to create a bank transfer file. The amount column may not contain decimals, and must be padded with zeros. However, the syntax that I use causes the amount field to sometimes drop 1c. The point at which this happens is when I do the cast(sum(amount) * 100 as int). Before I convert to int, the values are still correct. As soon as I convert to int, it drops 1c off the end.
I am able to replicate the behavior with the following code:
CREATE TABLE dbo.test_conv(
[supplier] [char](8) NOT NULL DEFAULT (' '),
[amount] [float] NOT NULL DEFAULT ((0))
) ON [PRIMARY]
insert into dbo.test_conv values ('SUP01', 33117.38)
insert into dbo.test_conv values ('SUP02', 33117.34)
SELECT supplier, sum(amount *100) as int_val,
cast(left( replicate( '0', 11 ), 11 - len(cast(cast(sum(amount) *100 as int) as varchar(11))) ) + cast(cast(sum(amount) *100 as int) as varchar(11)) as char(11)) as amount from test_conv group by supplier
Have a look at “int_val2” – it drops 1c…
If you add a few more examples, you will see that it doesn’t happen for all values:
insert into dbo.test_conv values ('SUP03', 804.11)
insert into dbo.test_conv values ('SUP04', 10109.36)
insert into dbo.test_conv values ('SUP04', 25000)
insert into dbo.test_conv values ('SUP05', 33711.38)
insert into dbo.test_conv values ('SUP06', 1226.64)
insert into dbo.test_conv values ('SUP07', 11853.05)
In the list above, only 'SUP05', 33711.38 has the problem
If I do not convert to int, I get the following error:
Arithmetic overflow error for type varchar, value = 3311738.000000. (for the value 33711.38)
January 22, 2010 at 7:41 am
The problem is the datatype you are using for amount. Here is some code I put together that appears to work, give it a try.
SELECT
supplier,
sum(cast(round(amount * 100,0) as int)) as int_val,
right(replicate('0',11) + cast(sum(cast(round(amount * 100,0) as int)) as varchar(11)), 11) as amount
from
test_conv
group by
supplier
January 22, 2010 at 2:36 pm
REPLACE(
STR(sum(amount), 11,2),
' ', '0')
Is it what you are looking for?
_____________
Code for TallyGenerator
January 24, 2010 at 6:09 pm
This works PERFECTLY - thanks very much, Lynn!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply