SQL error Converting data to Int When I don't ask for it
I have the following SQL statement. In it, I need to convert some numbers stored as varchar to decimal in order to sum them.
When I run this SQL against my limits, I get this message:
Msg 245, Level 16, State 1, Line 1 Conversion failed while converting varchar '1635.34' to int.
Which puzzles me because I am passing my data as decimal. It also confuses me, because when I use different constraints that have the same data type in the field ( 1234.56
format type), it works. This data is in the field TotalPremium
.
(My logic is a bit tricky, so the SQL statement is tricky. I'm posting this all for clarity. Also, renaming the field type of a database table is not an option at the moment.)
SELECT Account_No, version_num, LineOfBus, ProductNo, QuoteNo, Sum(Cast(TotalPremium as Decimal(16,2))) TotalPremium
FROM
(SELECT t.Account_No, t.version_num,
CASE
WHEN t.PackageIndicator = '1' THEN 'Package' Else t.Lob
END AS LineOfBus,
t.ProductNo, t.QuoteNo, Cast(COALESCE(t.TotalPremium,0) as decimal(16,2)) TotalPremium
FROM uAccountProductInfo as T
WHERE t.version_num IN
(SELECT sqVersionNumber.version_num
FROM
/* this captures unique package product records (or just stand alone records as well) */
(SELECT DISTINCT sqUnique.version_num, Count(sqUnique.version_num) VersionCount
FROM
/* grab list of all uniquer version, product, quote combinations (use distinct to combine package */
(SELECT DISTINCT version_num, productNo, quoteNo
FROM uAccountProductInfo
WHERE Account_No = '1172014' /* pass as parameter */
AND ProductNo IN ('6472930', '6474927') /* pass as parameter */
AND QuoteNo IN ('724185-01', '881957-08') /* pass as parameter */
) AS sqUnique
GROUP BY version_num
HAVING Count(version_num) = 2 /* pass as variable based on number of products, quotes */
) as sqVersionNumber
)
AND t.Account_no = '1172014' /* pass as parameter */
AND t.ProductNo IN ('6472930', '6474927') /* pass as parameter */
AND t.QuoteNo IN ('724185-01', '881957-08') /* pass as parameter */) as sqLOB
GROUP BY Account_No, version_num, LineOfBus, ProductNo, QuoteNo
source to share
The problem is that SQL Server does not guarantee the order in which operations are evaluated. You clearly have something inappropriate about the field. In SQL Server 2012+ use try_convert()
:
SELECT Sum(try_convert(decimal(16, 2), TotalPremium ))) as TotalPremium
In earlier versions, use case
:
SELECT Sum(case when isnumeric(TotalPremium) = 1 then convert(decimal(16, 2), TotalPremium)) end) as TotalPremium
isnumeric()
not perfect, but it should be good enough for your purposes.
source to share
Transfer t.TotalPremium
to decimal before concatenation. Your query runs coalesce
for a string and an integer and then returns the result in decimal. 0.0 0 Try using
instead of
as well.
edit I really don't think using 0.0
and not 0
is a good idea other than reading. If that's the goal, apply it to the same decimal data type. Otherwise, it could be interpreted as the dominant data type over decimal
. 0
how int
or varchar
shouldn't take precedence over our non-decimal value.
source to share
You can use isnull()
instead coalesce()
, although it would be better to use the same datatype as RexMaison.
create table t (TotalPremium varchar(16));
insert into t values (''),(null),('1635.34');
/* no error */
select isnull(t.TotalPremium,0)
from t;
/* no error */
select coalesce(t.TotalPremium,'0')
from t;
/* error */
select coalesce(t.TotalPremium,0)
from t;
Demo version of the rexter: http://rextester.com/OHEJ71310
source to share
Just post the final code that worked after including items from all 3 answers.
SELECT Account_No, version_num, LineOfBus, ProductNo, QuoteNo,
SUM(CASE
WHEN ISNUMERIC(TotalPremium) = 1 THEN CONVERT(decimal(16,2),TotalPremium)
END) As TotalPremium
FROM
(SELECT t.Account_No, t.version_num,
CASE
WHEN ISNull(t.PackageIndicator,'0') = '1' THEN 'Package' Else t.Lob
END AS LineOfBus,
t.ProductNo, t.QuoteNo,
ISNull(CASE
WHEN ISNUMERIC(t.TotalPremium) = 1 THEN CONVERT(decimal(16,2),t.TotalPremium)
END, 0) TotalPremium
FROM uAccountProductInfo as T
WHERE t.version_num IN
(SELECT sqVersionNumber.version_num
FROM
/* this captures unique package product records (or just stand alone records as well) */
(SELECT DISTINCT sqUnique.version_num, Count(sqUnique.version_num) VersionCount
FROM
/* grab list of all uniquer version, product, quote combinations (use distinct to combine package */
(SELECT DISTINCT version_num, productNo, quoteNo
FROM uAccountProductInfo
WHERE Account_No = '1172014' /* pass as parameter */
AND ProductNo IN ('6472930', '6474927') /* pass as parameter */
AND QuoteNo IN ('724185-01', '881957-08') /* pass as parameter */
) AS sqUnique
GROUP BY version_num
HAVING Count(version_num) = 2 /* pass as variable based on number of products, quotes */
) as sqVersionNumber
)
AND t.Account_no = '1172014' /* pass as parameter */
AND t.ProductNo IN ('6472930', '6474927') /* pass as parameter */
AND t.QuoteNo IN ('724185-01', '881957-08') /* pass as parameter */) as sqLOB
GROUP BY Account_No, version_num, LineOfBus, ProductNo, QuoteNo
source to share