Error Converting to Numeric in Where Clause 2

[briangriffin]

This query works fine:

select 
b.VisitID,
cast(c.ResultRW as numeric(20,2)),
isnumeric(c.ResultRW)

from Livendb.dbo.LabRequisitions a inner join Livendb.dbo.LabSpecimens b
	on a.VisitID = b.VisitID
	and a.RequisitionID = b.RequisitionID
	and a.SourceID = b.SourceID
	
inner join Livendb.dbo.LabSpecimenTests c
	on a.VisitID = c.VisitID
	and b.SpecimenID = c.SpecimenID
	and b.SourceID = c.SourceID
	and isnumeric(c.ResultRW) = 1
	
inner join Livendb.dbo.DLabTest e
	on c.TestPrintNumberID = e.PrintNumberID
	and e.Mnemonic in ('GLU','GLUBS','.HBGA1C')
	
Where 
[b]--cast(c.ResultRW as numeric(20,2)) >= 180
[/b]and c.ResultDateTime is not null

order by 2

[/code}

However, when I uncomment the 'cast as numeric' line in the where clause, I get the 'error converting data type varchar to numeric' message. Why don't I get that same message from that conversion in the select clause? And how can I get around this efficiently? I get the same error when moving that line to the join. 

TIA.

 

[bborissov]

What result returns this query?

SELECT *
FROM Livendb.dbo.LabSpecimenTests c
Where ISNUMERIC(c.ResultRW+'e0') = 0 
  and c.ResultDateTime is not null

Borislav Borissov
VFP9 SP2, SQL Server

 

[briangriffin]

It returns a complete dataset. However, when I modify my where clause I still get the conversion error.

 

[bborissov]

The you have records which can not be converted to numeric.
try this:

select b.VisitID,
       cast(c.ResultRW as numeric(20,2)),
       isnumeric(c.ResultRW)
from Livendb.dbo.LabRequisitions a 
inner join Livendb.dbo.LabSpecimens b on a.VisitID = b.VisitID 
                                     and a.RequisitionID = b.RequisitionID
                                     and a.SourceID = b.SourceID
inner join Livendb.dbo.LabSpecimenTests c on a.VisitID = c.VisitID
                                         and b.SpecimenID = c.SpecimenID
                                         and b.SourceID = c.SourceID
                                         and isnumeric(c.ResultRW+'e0') = 1
inner join Livendb.dbo.DLabTest e on c.TestPrintNumberID = e.PrintNumberID
                                 and e.Mnemonic in ('GLU','GLUBS','.HBGA1C')

Where isnumeric(c.ResultRW+'e0') = 1
 AND cast(c.ResultRW as numeric(20,2)) >= 180
 and c.ResultDateTime is not null
order by 2

Borislav Borissov
VFP9 SP2, SQL Server

 

[Olaf Doschke]

That means ISNUMERRIC(c.ResultRW) = 1, but ISNUMERIC(c.ResultRW+'e0') = 0, which indicates a problem. If you have a simple number, you can also decorate it with 'e0', which simply means *10^0 in scientific notation.
So what's in c.ResultRW? Varchar for sure, but can CAST really work on the values?

If cast fails you still get NULL or smoething else, but you can't commpare NULL with >=180, which is why your where clause still fails.

Bye, Olaf.

 

[briangriffin]

Same error. I don't get why it doesn't throw that error in the select clause, if the data isn't able to be converted.

So I tried this:

with cte_tests as
(
select b.VisitID,
cast(c.ResultRW as numeric(20,2)) as NumericResult
  
from Livendb.dbo.LabRequisitions a 
inner join Livendb.dbo.LabSpecimens b on a.VisitID = b.VisitID 
                                     and a.RequisitionID = b.RequisitionID
                                     and a.SourceID = b.SourceID
inner join Livendb.dbo.LabSpecimenTests c on a.VisitID = c.VisitID
                                         and b.SpecimenID = c.SpecimenID
                                         and b.SourceID = c.SourceID
                                         and isnumeric(c.ResultRW+'e0') = 1
inner join Livendb.dbo.DLabTest e on c.TestPrintNumberID = e.PrintNumberID
                                 and e.Mnemonic in ('GLU','GLUBS','.HBGA1C')

Where isnumeric(c.ResultRW) = 1
 and c.ResultDateTime is not null)
 
 select * from cte_tests where NumericResult >= 180

But got the same error, so I went with this:

declare @v_results table
(VisitID varchar(30),
NumericResult numeric(20,2))

insert into @v_results

select b.VisitID,
cast(c.ResultRW as numeric(20,2)) as NumericResult
  
from Livendb.dbo.LabRequisitions a 
inner join Livendb.dbo.LabSpecimens b on a.VisitID = b.VisitID 
                                     and a.RequisitionID = b.RequisitionID
                                     and a.SourceID = b.SourceID
inner join Livendb.dbo.LabSpecimenTests c on a.VisitID = c.VisitID
                                         and b.SpecimenID = c.SpecimenID
                                         and b.SourceID = c.SourceID
                                         inner join Livendb.dbo.DLabTest e on c.TestPrintNumberID = e.PrintNumberID
                                 and e.Mnemonic in ('GLU','GLUBS','.HBGA1C')

Where isnumeric(c.ResultRW) = 1
 and c.ResultDateTime is not null
 
 
 select * from @v_results where NumericResult >= 180

Which does work, but is pretty slow.

 

[gmmastros]

I've had this problem before. With SQL Server, you don't get to decide the order of evaluation, the database engine does. For some reason, the engine decided to process the select clause before the where clause. This situation is relatively rare, but can certainly occur.

Try the isnumeric check in the select clause, like this:

select 
b.VisitID,
cast(Case When IsNumeric(c.ResultRW + 'e0') = 1 Then c.ResultRW End as numeric(20,2)),
isnumeric(c.ResultRW)

from Livendb.dbo.LabRequisitions a inner join Livendb.dbo.LabSpecimens b
on a.VisitID = b.VisitID
and a.RequisitionID = b.RequisitionID
and a.SourceID = b.SourceID

inner join Livendb.dbo.LabSpecimenTests c
on a.VisitID = c.VisitID
and b.SpecimenID = c.SpecimenID
and b.SourceID = c.SourceID
and isnumeric(c.ResultRW) = 1

inner join Livendb.dbo.DLabTest e
on c.TestPrintNumberID = e.PrintNumberID
and e.Mnemonic in ('GLU','GLUBS','.HBGA1C')

Where 
cast(Case When IsNumeric(c.ResultRW + 'e0') = 1 Then c.ResultRW End as numeric(20,2)) >= 180
and c.ResultDateTime is not null

order by 2

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[briangriffin]

Yes, that worked and I see why, thank you.