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 NEndIt'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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy