Click here to Skip to main content
16,021,911 members
Articles / Database Development / SQL Server
Tip/Trick

Types Don't Match between the Anchor and the Recursive Part in Column...

Rate me:
Please Sign up or sign in to vote.
5.00/5 (5 votes)
28 Dec 2019CPOL1 min read 18.1K   19   1   2
How to overcome the error message "Types don't match between the anchor and the recursive part in column..." for varchar columns in a recursive CTE query

The Problem

A quite common requirement is to introduce a path column in a recursive CTE query. With a path, I mean a column which gathers information from the previous levels in the same hierarchy. An easy way to do this is to concatenate data from previous levels to the current row.

For example, if you would need to present locations hierarchically, you could have the following table:

SQL
CREATE TABLE Locations (
   Id           int,
   ParentId     int,
   LocationType varchar(100),
   LocationName varchar(100)
)

and the data:

SQL
INSERT INTO Locations (Id, ParentId, LocationType, LocationName) VALUES
(1,  null, 'Continent', 'Europe'),
(2,  1,    'Country',   'France'),
(3,  2,    'City',      'Paris'),
(4,  2,    'City',      'Marseille'),
(5,  2,    'City',      'Lyon'),
(6,  1,    'Country',   'Italy'),
(7,  6,    'City',      'Rome'),
(8,  6,    'City',      'Milan'),
(9,  6,    'City',      'Venice'),
(10, 1,    'Country',   'United Kingdom'),
(11, 10,   'City',      'London'),
(12, 10,   'City',      'Cambridge'),
(13, 10,   'City',      'Bath');

The requirement could contain that you need to show for each city, where it is located (continent and country). We can do this with a small recursive CTE like the following:

SQL
-- This statement fails with error:
-- Types don't match between the anchor and the recursive part in column 
-- "FullLocationType" of recursive query "Places".
WITH Places (Id, ParentId, LocationName, Level, FullLocationType, FullLocationName) AS (
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          1,
          l.LocationType,
          l.LocationName
   FROM Locations l
   WHERE l.ParentId IS NULL
   UNION ALL
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          p.Level + 1,
          CONCAT(p.FullLocationType, ' / ', l.LocationType),
          CONCAT(p.FullLocationName, ' / ', l.LocationName)
   FROM Locations l
   INNER JOIN Places p ON p.Id = l.ParentId
)
SELECT  p.Level,
        p.FullLocationType,
        p.FullLocationName
FROM Places P
ORDER BY p.Id;

But when running this query, you'll end up receiving an error message:

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

Why is this? As we can see, both parts of the recursive query contain a varchar column for FullLocationType.

The thing is that even though the actual data type matches, for character types also the length must match. The definition for the column was varchar(100) but the concatenation produces a string with maximum data length.

So an easy fix is to convert the initial data to the same length as the concatenation produces. To do this, you can use CAST and CONVERT functions. Here's the modified query:

SQL
WITH Places (Id, ParentId, LocationName, Level, FullLocationType, FullLocationName) AS (
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          1,
          CAST(l.LocationType AS varchar(max)),
          CAST(l.LocationName AS varchar(max))
   FROM Locations l
   WHERE l.ParentId IS NULL
   UNION ALL
   SELECT l.Id,
          l.ParentId,
          l.LocationName,
          p.Level + 1,
          CONCAT(p.FullLocationType, ' / ', LocationType),
          CONCAT(p.FullLocationName, ' / ', l.LocationName)
   FROM Locations l
   INNER JOIN Places p ON p.Id = l.ParentId
)
SELECT  p.Level,
        p.FullLocationType,
        p.FullLocationName
FROM Places P
ORDER BY p.Id;

When executed, the query now happily returns the results:

Level   FullLocationType             FullLocationName
-----   --------------------------   -------------------------------------
1       Continent                    Europe
2       Continent / Country          Europe / France
3       Continent / Country / City   Europe / France / Paris
3       Continent / Country / City   Europe / France / Marseille
3       Continent / Country / City   Europe / France / Lyon
2       Continent / Country          Europe / Italy
3       Continent / Country / City   Europe / Italy / Rome
3       Continent / Country / City   Europe / Italy / Milan
3       Continent / Country / City   Europe / Italy / Venice
2       Continent / Country          Europe / United Kingdom
3       Continent / Country / City   Europe / United Kingdom / London
3       Continent / Country / City   Europe / United Kingdom / Cambridge
3       Continent / Country / City   Europe / United Kingdom / Bath

Points of Interest

An interesting thing is that this problem does not occur with a regular UNION statement. Consider the following example:

SQL
SELECT CAST('A' AS varchar(100))
UNION ALL
SELECT CONCAT('A', 'B', 'C');

Without any problems, it returns:

(No column name)
----------------
A
ABC

References

History

  • 29th December, 2019: Created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect
Europe Europe
Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pin
dmjm-h3-Jan-20 4:27
dmjm-h3-Jan-20 4:27 
GeneralRe: My vote of 5 Pin
Wendelius4-Jan-20 2:02
mentorWendelius4-Jan-20 2:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.