SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filtering on CTE result


Filtering on CTE result

Author
Message
cgreathouse
cgreathouse
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 371
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

1 10
1000 1010
2000 2100

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!
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5834 Visits: 5080
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
cgreathouse
cgreathouse
SSC-Enthusiastic
SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)SSC-Enthusiastic (103 reputation)

Group: General Forum Members
Points: 103 Visits: 371
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!
Igor Micev
Igor Micev
SSCertifiable
SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)SSCertifiable (5.8K reputation)

Group: General Forum Members
Points: 5834 Visits: 5080
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,
SQL Server developer at Seavus
My blog: www.igormicev.com
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2022 Visits: 10344
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.


Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
TomThomson
TomThomson
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14202 Visits: 12197
Igor Micev (3/16/2014)
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

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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search