March 15, 2014 at 12:33 pm
I'm a little confused on why this sql isn't working as expected. First here's the sql
declare @table table(start varchar(4)
,[end] varchar(4))
insert @table(start, [end])
values ('0001', '0010')
,('1000', '1010')
,('10BN', '10BN')
,('2000', '2100')
declare @testVal smallint
set @testVal = 2010
;with NumericOnly
as
(
select cast(start as smallint) NStart, cast([end] as smallint) NEnd
from @table
where start not like '%[^0-9]%'
and [end] not like '%[^0-9]%'
)
select *
from NumericOnly
where @testVal between NStart and NEnd
when I run this I get the error
Conversion failed when converting the varchar value '10BN' to data type smallint
If I comment out the last where clause I get
110
10001010
20002100
So I'm confused on why it's complaining about '10BN'.
What I've done to work around this is to first put the results from NumericOnly into a table and then apply the filter to that table. What I'm curious about is why I got the error in the first place.
Anybody have any ideas?
Thanks!
March 15, 2014 at 5:41 pm
Hi
One solution is to use conversion for @testVal
select *
from NumericOnly
where convert(varchar(4),@testVal) between NStart and NEnd
Another solution is to use a temp tables like for e.g. below:
declare @NumericOnly table(Nstart smallint,Nend smallint)
insert into @NumericOnly
select convert(smallint,start) NStart, convert(smallint,[end]) NEnd
from @table
where start not like '%[^0-9]%'
and [end] not like '%[^0-9]%'
select *
from @NumericOnly
where @testVal between NStart and NEnd
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
March 15, 2014 at 6:08 pm
Thanks Igor, however I was looking for a solution. I was able to put the results into a table first and then use that table, just like you said in your 2nd solution.
What I was looking for was an explanation why i was getting the error in the first place. It seems like the results coming from the CTE should filter out the values that are non-numeric. I don't understand why I was seeing the error in the first place.
Thanks!
March 16, 2014 at 6:39 am
Hi
This is also a solution:
declare @table table(start varchar(4)
,[end] varchar(4))
insert @table(start, [end])
values ('0001', '0010')
,('1000', '1010')
,('10BN', '10BN')
,('2000', '2100')
declare @testVal smallint
set @testVal = 2010
;with NumericOnly
as
(select
case when ([start] not like '%[^0-9]%' and [end] not like '%[^0-9]%') then cast([start] as smallint) else 0 end [NStart],
case when ([start] not like '%[^0-9]%' and [end] not like '%[^0-9]%') then cast([end] as smallint) else 0 end [NEnd]
from @table
)
select *
from NumericOnly
where @testVal between NStart and NEnd
It's the conversion. In your case your CTE is just a view on the table's data, and then you're trying to make a comparison/filtering with different data type.
In this case the CTE has real cast data.
Regards,
Igor
Igor Micev,My blog: www.igormicev.com
March 16, 2014 at 9:38 am
cgreathouse (3/15/2014)
Thanks Igor, however I was looking for a solution. I was able to put the results into a table first and then use that table, just like you said in your 2nd solution.What I was looking for was an explanation why i was getting the error in the first place. It seems like the results coming from the CTE should filter out the values that are non-numeric. I don't understand why I was seeing the error in the first place.
Thanks!
Results don't "come from a CTE". Except for recursive CTE's, they are incorporated into the body of the main query in a similar way to a view.
SQL Server will perform operations in whichever order is calculated as "best". The conversion from varchar to int can occur either before or after the numeric-only filter. In this case, before.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 16, 2014 at 12:59 pm
Igor Micev (3/16/2014)
HiThis is also a solution:
declare @table table(start varchar(4),[end] varchar(4))
insert @table(start, [end])
values ('0001', '0010')
,('1000', '1010')
,('10BN', '10BN')
,('2000', '2100')
declare @testVal smallint
set @testVal = 2010
;with NumericOnly
as
(select
case when ([start] not like '%[^0-9]%' and [end] not like '%[^0-9]%') then cast([start] as smallint) else 0 end [NStart],
case when ([start] not like '%[^0-9]%' and [end] not like '%[^0-9]%') then cast([end] as smallint) else 0 end [NEnd]
from @table
)
select *
from NumericOnly
where @testVal between NStart and NEnd
It's the conversion. In your case your CTE is just a view on the table's data, and then you're trying to make a comparison/filtering with different data type.
In this case the CTE has real cast data.
Regards,
Igor
I think you need to change "cast([start] as smallint) else 0 end [NStart]" to "cast([start] as smallint) else 1 end [NStart]" to avoid the obvious error when @testval is 0.
Tom
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply