none
Types don't match between the anchor and the recursive part in column RRS feed

  • Question

  • I am trying to separate comma separate values into rows and I am getting this error:

    Types don't match between the anchor and the recursive part in column "DataItem" of recursive query "tmp".

    ;with tmp(Id, Name, DataItem, Visitor) as (
    select Id, Name, LEFT(Visitor, CHARINDEX(',',Visitor+',')-1),
        STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), '')
    from TestTable
    union all
    select Id, Name, LEFT(Visitor, CHARINDEX(',',Visitor+',')-1),
        STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), '')
    from tmp
    where Visitor > ''
    )
    select Id, Name, DataItem
    from tmp
    order by Id

    
    


    • Edited by bbt2d Thursday, April 4, 2013 4:05 PM Resized text
    Thursday, April 4, 2013 4:03 PM

Answers

  • Try CONVERT to resolve the issue:

    ;with tmp(Id, Name, DataItem, Visitor) as (
    select Id, Name, CONVERT(varchar(100),LEFT(Visitor, CHARINDEX(',',Visitor+',')-1)),
        CONVERT(varchar(100),STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), ''))
    from TestTable
    union all
    select Id, Name, CONVERT(varchar(100),LEFT(Visitor, CHARINDEX(',',Visitor+',')-1)),
        CONVERT(varchar(100),STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), ''))
    from tmp
    where Visitor > ''
    )
    select Id, Name, DataItem
    from tmp
    order by Id


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    • Marked as answer by bbt2d Thursday, April 4, 2013 6:10 PM
    Thursday, April 4, 2013 5:18 PM
    Moderator
  • Just CAST the result of the LEFT function to be the same type and length as Visitor.  So if Visitor is varchar(50), then

    ;with tmp(Id, Name, DataItem, Visitor) as (
    select Id, Name, CAST(LEFT(Visitor, CHARINDEX(',',Visitor+',')-1) As varchar(50)),
        STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), '')
    from TestTable
    union all
    select Id, Name, CAST(LEFT(Visitor, CHARINDEX(',',Visitor+',')-1) As varchar(50)),
        STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), '')
    from tmp
    where Visitor > ''
    )
    select Id, Name, DataItem
    from tmp
    order by Id

    Tom

    • Marked as answer by bbt2d Thursday, April 4, 2013 6:10 PM
    Thursday, April 4, 2013 5:19 PM

All replies

  • Try CONVERT to resolve the issue:

    ;with tmp(Id, Name, DataItem, Visitor) as (
    select Id, Name, CONVERT(varchar(100),LEFT(Visitor, CHARINDEX(',',Visitor+',')-1)),
        CONVERT(varchar(100),STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), ''))
    from TestTable
    union all
    select Id, Name, CONVERT(varchar(100),LEFT(Visitor, CHARINDEX(',',Visitor+',')-1)),
        CONVERT(varchar(100),STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), ''))
    from tmp
    where Visitor > ''
    )
    select Id, Name, DataItem
    from tmp
    order by Id


    Kalman Toth Database & OLAP Architect sqlusa.com
    New Book / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    • Marked as answer by bbt2d Thursday, April 4, 2013 6:10 PM
    Thursday, April 4, 2013 5:18 PM
    Moderator
  • Just CAST the result of the LEFT function to be the same type and length as Visitor.  So if Visitor is varchar(50), then

    ;with tmp(Id, Name, DataItem, Visitor) as (
    select Id, Name, CAST(LEFT(Visitor, CHARINDEX(',',Visitor+',')-1) As varchar(50)),
        STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), '')
    from TestTable
    union all
    select Id, Name, CAST(LEFT(Visitor, CHARINDEX(',',Visitor+',')-1) As varchar(50)),
        STUFF(Visitor, 1, CHARINDEX(',',Visitor+','), '')
    from tmp
    where Visitor > ''
    )
    select Id, Name, DataItem
    from tmp
    order by Id

    Tom

    • Marked as answer by bbt2d Thursday, April 4, 2013 6:10 PM
    Thursday, April 4, 2013 5:19 PM