CODEWITHSUNDEEP
sqlsql-server
gustavhf
gustavhf

Reputation: 41

Msg 240, Types don't match between the anchor and the recursive part in column

I am trying to split a row when the ';' character appears. I have a list with projects, where one of the columns states which district the project belongs to. However some projects appears in multiple districts and is therefore written like "1;2;3" (District 1, 2 and 3). I want to create three rows form this and split on the ';'.

The error message says:

Msg 240, Level 16, State 1, Line 13
Types don't match between the anchor and the recursive part in column "DataItem" of recursive query "tmp".

I tried the split_string, but discovered my server is 2014 and lacks compatibility.

WITH tmp(Oppdragsnr, Kommune, DataItem, Kommunenr) AS
(
    SELECT
        Oppdragsnr,
        Kommune,
        LEFT(Kommunenr, CHARINDEX(';', Kommunenr + ';') - 1),
        STUFF(Kommunenr, 1, CHARINDEX(';', Kommunenr + ';'), '')
    FROM 
        oppdragene

    UNION all

    SELECT
        Oppdragsnr,
        Kommune,
        LEFT(Kommunenr, CHARINDEX(';', Kommunenr + ';') - 1),
        STUFF(Kommunenr, 1, CHARINDEX(';', Kommunenr + ';'), '')
    FROM  
        tmp
    WHERE
        Kommunenr > ''
)
SELECT
    Oppdragsnr,
    Kommune,
    DataItem
FROM 
    tmp
ORDER BY 
    Oppdragsnr

I would like the output to be a new table with new rows for every project that appears in multiple districts.

Upvotes: 3

Views: 1938

Answers (1)

MJoy
MJoy

Reputation: 1369

You should probably CAST to INT the DataItem column in the base and recursive part of the query, the following query should work for you

;WITH tmp(Oppdragsnr, Kommune, DataItem, Kommunenr) AS
(
    SELECT
        Oppdragsnr,
        Kommune,
        CAST(LEFT(Kommunenr, CHARINDEX(';', Kommunenr + ';') - 1) AS INT),
        STUFF(Kommunenr, 1, CHARINDEX(';', Kommunenr + ';'), '')
    FROM 
        oppdragene

    UNION all

    SELECT
        Oppdragsnr,
        Kommune,
        CAST(LEFT(Kommunenr, CHARINDEX(';', Kommunenr + ';') - 1)  AS INT),
        STUFF(Kommunenr, 1, CHARINDEX(';', Kommunenr + ';'), '')
    FROM  
        tmp
    WHERE
        Kommunenr > ''
)
SELECT
    Oppdragsnr,
    Kommune,
    DataItem
FROM 
    tmp
ORDER BY 
    Oppdragsnr

Upvotes: 3

Related Questions