Filtering on CTE result

  • 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!

  • 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

  • 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!

  • 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

  • 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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

  • 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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply