Types don't match between the anchor and the recursive part in column "manager

I always get the error

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

on the following query, even when CASTing all columns, what am I doing wrong?

WITH manager (
    full_name,
    first_name,
    email,
    crm_user_id,
    "role",
    parent_role,
    manager_name,
    manager_email,
    crm_manager_id,
    role_path,
    manager_path
)
AS (
    SELECT
        CAST(full_name as VARCHAR(512)),
        CAST(first_name as VARCHAR(512)),
        CAST(email as VARCHAR(512)),
        CAST(crm_user_id as VARCHAR(18)),
        CAST([role] as VARCHAR(128)),
        CAST(parent_role as VARCHAR(128)),
        CAST(NULL as VARCHAR(512)),
        CAST(NULL as VARCHAR(512)),
        CAST(NULL as VARCHAR(18)),
        CAST([role] as VARCHAR(max)),
        CAST(full_name as VARCHAR(max))
    FROM dbo.Forecast_owners
    WHERE parent_role IS NULL
UNION ALL
    SELECT
        CAST(employee.full_name as VARCHAR(512)),
        CAST(employee.first_name as VARCHAR(512)),
        CAST(employee.email as VARCHAR(512)),
        CAST(employee.crm_user_id as VARCHAR(18)),
        CAST(employee.[role] as VARCHAR(128)),
        CAST(employee.parent_role as VARCHAR(128)),
        CAST(manager.full_name as VARCHAR(512)),
        CAST(manager.email as VARCHAR(512)),
        CAST(manager.crm_user_id as VARCHAR(18)),
        CAST((manager.role_path   '/'   employee.[role]) as VARCHAR(max)),
        CAST((manager.manager_path   '/'   employee.full_name) as VARCHAR(max))
    FROM
        dbo.Forecast_owners employee
    JOIN manager ON employee.parent_role = manager.[role]
)
SELECT 
    *
FROM
    manager

Table DDL is as follows:

CREATE TABLE Forecast_owners (
    full_name varchar(512) COLLATE Latin1_General_CI_AS NULL,
    first_name varchar(512) COLLATE Latin1_General_CI_AS NULL,
    email varchar(512) COLLATE Latin1_General_CI_AS NULL,
    crm_user_id varchar(18) COLLATE Latin1_General_CI_AS NULL,
    [role] varchar(128) COLLATE Latin1_General_CI_AS NULL,
    parent_role varchar(128) COLLATE Latin1_General_CI_AS NULL
);

Running

SELECT CONVERT (varchar(256), SERVERPROPERTY('collation'));

I get the Server Collation to be SQL_Latin1_General_CP1_CI_AS

CodePudding user response：

Almost certainly your database's default collation does not match the collation specified on these three columns:

    first_name varchar(512) COLLATE Latin1_General_CI_AS NULL,
    email varchar(512) COLLATE Latin1_General_CI_AS NULL,
    crm_user_id varchar(18) COLLATE Latin1_General_CI_AS NULL,

In the anchor part of your statement you could replace this part:

        CAST(NULL as VARCHAR(512)),
        CAST(NULL as VARCHAR(512)),
        CAST(NULL as VARCHAR(18)),

With this:

        CAST(NULL   '' COLLATE Latin1_General_CI_AS as VARCHAR(512)),
        CAST(NULL   '' COLLATE Latin1_General_CI_AS as VARCHAR(512)),
        CAST(NULL   '' COLLATE Latin1_General_CI_AS as VARCHAR(18)),

And I would expect your error to be corrected.