UNION ALL第8列DOUBLE与STRING类型不兼容错误调试求助

错误原因 #

错误提示明确指出UNION ALL的第8列（对应ACTUAL字段）类型不兼容：第一个查询中ACTUAL是DOUBLE类型，第二个查询里你用" "生成的是STRING类型，两者无法直接合并。

两种修正方案 #

方案1：将第二个查询的占位字段转为DOUBLE类型 #

如果希望保留数值类型，把第二个查询里的字符串占位符改成对应数值类型的空值：

SELECT
    LOCATION,
    DEPARTMENT,
    MANAGER,
    SUPERVISOR,
    ASSOCIATE,
    FISCAL_DATE,
    AUX_NAME,
    ACTUAL, 
    PLANNED, 
    " " FSCL_YR,
    " " FSCL_WK,
    " " FSCL_MTH_NM,
    " " FSCL_YR_WK,
    " " FSCL_DT,
    " " ORG_NM,
    " " SRMGR_PRSN_NM,
    " " DIR_PRSN_NM
FROM
    `analytics-views-thd.CONTACT_CENTERS.CC_ASSOCIATE_AUX_USAGE_UNPIVOT`
WHERE
    FISCAL_DATE >= DATE '2024-01-29'
UNION ALL
SELECT 
    " " LOCATION,
    " " DEPARTMENT,
    " " MANAGER,
    " " SUPERVISOR,
    " " ASSOCIATE,
    " " FISCAL_DATE,
    " " AUX_NAME,
    CAST(NULL AS DOUBLE) ACTUAL,  -- 转为DOUBLE类型的空值
    CAST(NULL AS DOUBLE) PLANNED, -- 转为DOUBLE类型的空值
    FSCL_YR,
    FSCL_WK,
    FSCL_MTH_NM,
    FSCL_YR_WK,
    FSCL_DT,
    ORG_NM,
    SRMGR_PRSN_NM,
    DIR_PRSN_NM
FROM
    `analytics-views-thd.CONTACT_CENTERS.AGENT_METRICS` 
WHERE
    FSCL_YR = 2024

方案2：将第一个查询的数值字段转为STRING类型 #

如果希望统一为字符串类型，把第一个查询的ACTUAL和PLANNED转成STRING：

SELECT
    LOCATION,
    DEPARTMENT,
    MANAGER,
    SUPERVISOR,
    ASSOCIATE,
    FISCAL_DATE,
    AUX_NAME,
    CAST(ACTUAL AS STRING) ACTUAL,  -- 转为STRING类型
    CAST(PLANNED AS STRING) PLANNED, -- 转为STRING类型
    " " FSCL_YR,
    " " FSCL_WK,
    " " FSCL_MTH_NM,
    " " FSCL_YR_WK,
    " " FSCL_DT,
    " " ORG_NM,
    " " SRMGR_PRSN_NM,
    " " DIR_PRSN_NM
FROM
    `analytics-views-thd.CONTACT_CENTERS.CC_ASSOCIATE_AUX_USAGE_UNPIVOT`
WHERE
    FISCAL_DATE >= DATE '2024-01-29'
UNION ALL
SELECT 
    " " LOCATION,
    " " DEPARTMENT,
    " " MANAGER,
    " " SUPERVISOR,
    " " ASSOCIATE,
    " " FISCAL_DATE,
    " " AUX_NAME,
    " " ACTUAL, 
    " " PLANNED, 
    FSCL_YR,
    FSCL_WK,
    FSCL_MTH_NM,
    FSCL_YR_WK,
    FSCL_DT,
    ORG_NM,
    SRMGR_PRSN_NM,
    DIR_PRSN_NM
FROM
    `analytics-views-thd.CONTACT_CENTERS.AGENT_METRICS` 
WHERE
    FSCL_YR = 2024

注意事项 #

• UNION ALL要求两个查询的列数一致，且对应位置的列类型完全匹配，包括精度、是否可为空等属性。
• 若之前尝试CAST未生效，大概率是只修改了其中一边字段，或CAST语法有误（比如未指定正确目标类型）。

内容的提问来源于stack exchange，提问作者Esty