Redshift下UNION视图出现类型不匹配与无效数字报错如何解决

问题原因 #

• 视图创建阶段类型不匹配报错：UNION ALL要求上下两个SELECT对应位置的字段类型必须完全一致，你的SQL中第一部分SELECT直接写的NULL未指定类型，Redshift会默认推断为整数类型，和第二部分对应位置的字符串类型字段不匹配；同时存在部分业务字段上下类型不一致的问题。
• 查询阶段整数转换报错：你第二部分SQL中将num_of_ques直接转integer类型，但该字段中存在带小数点的字符串值（如1.0、2.5），转换时小数点会被识别为非法字符导致报错。

修复步骤 #

1. 对第一部分SELECT中所有NULL值做显式类型转换，匹配第二部分对应字段的varchar类型
2. 统一Number of Questions字段的转换逻辑，可使用Redshift原生的TRY_CAST函数，转换失败时返回NULL而非直接中断执行
3. 对第二部分SELECT中的NULL值也补充显式类型声明，避免类型推断误差

修复后完整代码 #

DROP VIEW IF EXISTS jgbl.vw_jgvcc_crm_case_activity CASCADE;

CREATE OR REPLACE VIEW jgbl.vw_jgvcc_crm_case_activity
AS
SELECT case_number as "CASE Number",
       parent_case_number as "Parent Case Number",
       date_opened as "Date Opened",
       TRY_CAST(number_of_questions as integer) as "Number of Questions",
       case_record_type as "CASE Record Type",
       NULL::varchar as "Sub Type",
       category as "Category",
       NULL::varchar as "Sub Category",
       country as Country,
       customer_type as "Customer Type",
       primary_account_subtype as "Primary Account Subtype",
       source as Source,
       call_center_location as "Call Center Location",
       region as Region,
       customer_region as "Customer Region",
       NULL::varchar as "AE",
       NULL::varchar as "PQC",
       'ASPAC' as datasource
FROM JG_ASPAC.vw_jgvcc_aspac_crm_activity

UNION ALL
       
SELECT case_num as "CASE Number",
       NULL::varchar as "Parent Case Number",
       open_dt as "Date Opened",
       TRY_CAST(num_of_ques as integer) as "Number of Questions",
       rec_type_nm as "CASE Record Type",
       rec_sub_type as "Sub Type",
       cat_desc as "Category",
       sctgy_desc as "Sub Category",
       custm_latam_ctry_nm as Country,
       acct_type as "Customer Type",
       NULL::varchar as "Primary Account Subtype",
       src_in as Source,
       case when alph_fl='Y' then 'Alphanumeric' else 'LATAM Center' end as "Call Center Location",
       'LATAM' as Region,
       NULL::varchar as "Customer Region",  
       Case when rec_type_nm='AE/PQC' and rec_sub_type in ('ADVERSE EVENT','AE + PQC') then 'Y' else 'N' End as "AE",
       Case when rec_type_nm='AE/PQC' and rec_sub_type in ('AE + PQC', 'PRODUCT QUALITY COMPLAINT')  then 'Y' else 'N' End as "PQC",
       'LATAM' as datasource
FROM JG_LTM.vw_jgvcc_latam_crm_activity as v2
--LEFT JOIN jgbl.dim_iso_reg_cntry as t1 on t1.region = v2.ctry_iso2_cd 
with no schema binding;

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