October 28, 2014 at 5:29 am
I need to create a report that will return a list patient names if their INR lab results are greater than 4.0. The data type of the Results field is varchar(10). If the result is greater than 5.0, it is stored as >5.0.
select cast(replace(Result, '>', '') as decimal(3,2)) as 'Results' from dbo.TEST where Result>4.0
After running the above query I get an error, "Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."
Any thoughts?
create table dbo.TEST
(
Result varchar(25)
)
insert into dbo.TEST(Result) values('5.0')
insert into dbo.TEST(Result) values('4.2')
insert into dbo.TEST(Result) values('>5.0')
insert into dbo.TEST(Result) values('2.0')
insert into dbo.TEST(Result) values('4.8')
insert into dbo.TEST(Result) values('>5.0')
insert into dbo.TEST(Result) values('4.0')
insert into dbo.TEST(Result) values('0.5')
October 28, 2014 at 5:39 am
This will do the trick
SELECT *
FROM TEST
WHERE CAST(CASE Result WHEN '>5.0' THEN '6.0' ELSE Result END AS float) > 4.0
However, casting will make your predicate non-sargable.
-- Gianluca Sartori
October 28, 2014 at 5:40 am
NineIron (10/28/2014)
I need to create a report that will return a list patient names if their INR lab results are greater than 4.0. The data type of the Results field is varchar(10). If the result is greater than 5.0, it is stored as >5.0.select cast(replace(Result, '>', '') as decimal(3,2)) as 'Results' from dbo.TEST where Result>4.0
After running the above query I get an error, "Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric."
Any thoughts?
create table dbo.TEST
(
Result varchar(25)
)
insert into dbo.TEST(Result) values('5.0')
insert into dbo.TEST(Result) values('4.2')
insert into dbo.TEST(Result) values('>5.0')
insert into dbo.TEST(Result) values('2.0')
insert into dbo.TEST(Result) values('4.8')
insert into dbo.TEST(Result) values('>5.0')
insert into dbo.TEST(Result) values('4.0')
insert into dbo.TEST(Result) values('0.5')
How does this work for you? It separates the conversion from the comparison.
with cte(result) as (
select cast(replace(Result, '>', '') as decimal(3,2))
from dbo.test
)
select result
from cte
where Result > 4.0;
October 28, 2014 at 5:52 am
What does "non sargable" mean?
October 28, 2014 at 6:02 am
NineIron (10/28/2014)
What does "non sargable" mean?
It means it cannot be evaluated using an index.
-- Gianluca Sartori
October 28, 2014 at 6:03 am
spaghettidba (10/28/2014)
NineIron (10/28/2014)
What does "non sargable" mean?It means it cannot be evaluated using an index.
Which is usually a bad thing, performance wise.
-- Gianluca Sartori
October 28, 2014 at 6:06 am
Thanx.
October 28, 2014 at 6:08 am
Perfect. I did have to change the decimal(3,2) to decimal(10,2).
Thanx.
October 28, 2014 at 7:04 am
Glad it worked. Thanks for the feedback.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply