Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filtering on CTE result Expand / Collapse
Author
Message
Posted Saturday, March 15, 2014 12:33 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:30 PM
Points: 93, Visits: 328
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!
Post #1551516
Posted Saturday, March 15, 2014 5:41 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 2,934, Visits: 2,959
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
www.seavus.com
Post #1551526
Posted Saturday, March 15, 2014 6:08 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, May 9, 2014 1:30 PM
Points: 93, Visits: 328
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!
Post #1551529
Posted Sunday, March 16, 2014 6:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 11:12 AM
Points: 2,934, Visits: 2,959
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
www.seavus.com
Post #1551559
Posted Sunday, March 16, 2014 9:38 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:59 PM
Points: 1,090, Visits: 6,552
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
Post #1551571
Posted Sunday, March 16, 2014 12:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 5:07 AM
Points: 8,745, Visits: 9,293
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
Post #1551590
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse