My Table Data
Amount1 | Amount2 | Expected Result |
---|---|---|
100 | 200 | 4.17% |
A | 500 | |
500 | B | |
20 | 100 | 1.67% |
CREATE TABLE [dbo].[tblData](
[Amount1] [nvarchar](50) NULL,
[Amount2] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'100', N'200')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'A', N'500')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'', N'')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'500', N'B')
INSERT [dbo].[tblData] ([Amount1], [Amount2]) VALUES (N'20', N'100')
This works fine as expected when I run it on one line
SELECT TOP 1
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') AS Result
FROM tblData
This fails when I run it on whole table
SELECT
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%') AS Result
FROM tblData
Returning the below error message.
Error converting datatype nvarchar to numeric
CodePudding user response:
If you've got a text field that's mostly numeric but you can't rely on then instead of
cast(Amount1 AS decimal(18,4))
you could use
try_cast(Amount1 AS decimal(18,4))
Which returns Null if the conversion can't be done
I use try_convert but that's just personal preference, they're effectively the same
try_convert(decimal(18,4), Amount1 )
So the start of your statement would look like this
case
when try_cast(Amount1 as decimal(18,4)) is not null --Numeric
and try_cast(Amount2 as decimal(18,4)) > 0 --Numeric and not zero
then Concat(CAST((try_cast(Amount1 AS decimal(18,4))/(try_cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
CodePudding user response:
Check if columns are numeric before you do anything:
SELECT Amount1
, Amount2
, case when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
else
null
end AS "Expected Result"
FROM tblData
If you have 0's in your data then something like this:
SELECT Amount1, Amount2,
case when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 and Amount2 > 0 and Amount1 > 0 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 and Amount1 = 0 and Amount1 > 0 then
Concat(CAST((cast(1 AS decimal(18,4))/(cast(Amount2 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
when isnumeric(Amount1) = 1 and isnumeric(Amount2) = 1 and Amount1 > 0 and Amount1 = 0 then
Concat(CAST((cast(Amount1 AS decimal(18,4))/(cast(1 AS decimal(18,4))*12))*100 AS decimal(18,2)),'%')
else
null
end AS "Expected Result"
FROM tblData