Snowflake含WITH子句视图创建报错：无效列定义列表

错误原因分析 #

触发Invalid column definition list的核心原因是视图声明的列列表与查询返回的列在数量、名称上不匹配，原代码存在以下问题：

• 视图列列表中声明了CREATED_ON_DT、CHANGED_ON_DT，但子查询BUSN_LOCATION未从源表sq中选择这两列
• 子查询中错误地将CHANGED_BY.ROW_WID命名为CREATED_BY_WID，重复占用列名，导致CHANGED_BY_WID缺失
• 子查询中W_BUSN_LOCATION_D.W_UPDATE_DT as current_date别名错误，应改为W_UPDATE_DT（current_date是Snowflake内置函数，不能直接作为别名）
• 视图列数与子查询返回列数不一致，引发编译校验失败

修复后的视图创建代码 #

CREATE OR REPLACE VIEW TEST_RRX.DW_RRX.V_SIL_RRX_PTS_BusinessLocation_Dim(
  BUSN_LOC_NAME, 
  BUSN_LOC_NUM, 
  BUSN_LOC_TYPE, 
  ST_ADDRESS1, 
  ST_ADDRESS2, 
  CITY_NAME, 
  POSTAL_CODE, 
  STATE_CODE, 
  COUNTRY_CODE, 
  ACTIVE_FLG, 
  CREATED_BY_WID, 
  CHANGED_BY_WID, 
  CREATED_ON_DT, 
  CHANGED_ON_DT, 
  AUX1_CHANGED_ON_DT, 
  AUX2_CHANGED_ON_DT, 
  AUX3_CHANGED_ON_DT, 
  AUX4_CHANGED_ON_DT, 
  SRC_EFF_FROM_DT, 
  SRC_EFF_TO_DT, 
  EFFECTIVE_FROM_DT, 
  EFFECTIVE_TO_DT, 
  DELETE_FLG, 
  CURRENT_FLG, 
  W_INSERT_DT, 
  W_UPDATE_DT, 
  DATASOURCE_NUM_ID, 
  INTEGRATION_ID, 
  UPD_FLG
) AS WITH CODE AS (
  SELECT 
    W_CODE_D.MASTER_CODE AS MASTER_CODE, 
    W_CODE_D.MASTER_VALUE AS MASTER_VALUE, 
    W_CODE_D.SOURCE_CODE AS SOURCE_CODE, 
    W_CODE_D.DATASOURCE_NUM_ID AS DATASOURCE_NUM_ID, 
    W_CODE_D.SOURCE_CODE_1 as SOURCE_CODE_1, 
    W_CODE_D.SOURCE_CODE_2 as SOURCE_CODE_2, 
    W_CODE_D.SOURCE_NAME_1 as SOURCE_NAME_1, 
    W_CODE_D.CATEGORY 
  FROM 
    W_CODE_D 
  WHERE 
    W_CODE_D.LANGUAGE_CODE = 'E'
), 
USER_TAB as (
  SELECT 
    LOOKUP_TABLE.ROW_WID as ROW_WID, 
    LOOKUP_TABLE.DATASOURCE_NUM_ID as DATASOURCE_NUM_ID, 
    LOOKUP_TABLE.INTEGRATION_ID as INTEGRATION_ID, 
    LOOKUP_TABLE.EFFECTIVE_FROM_DT as EFFECTIVE_FROM_DT, 
    LOOKUP_TABLE.EFFECTIVE_TO_DT as EFFECTIVE_TO_DT 
  FROM 
    DW_RRX.W_USER_D LOOKUP_TABLE
), 
BUSN_LOCATION as (
  SELECT 
    sq.BUSN_LOC_NAME, 
    sq.BUSN_LOC_NUM, 
    sq.BUSN_LOC_TYPE, 
    sq.ST_ADDRESS1, 
    sq.ST_ADDRESS2, 
    CASE WHEN CITY.MASTER_VALUE IS NULL THEN (
      CASE WHEN sq.CITY_NAME IS NULL 
      OR LENGTH(sq.CITY_NAME) = 0 
      OR REGEXP_INSTR(sq.CITY_NAME, ' ') > 0 THEN 'Unspecified' ELSE sq.CITY_NAME END
    ) ELSE CITY.MASTER_VALUE END AS CITY_NAME, 
    sq.POSTAL_CODE, 
    CASE WHEN STATE.MASTER_CODE IS NULL THEN (
      CASE WHEN sq.STATE_CODE IS NULL 
      OR LENGTH(sq.STATE_CODE) = 0 
      OR REGEXP_INSTR(sq.STATE_CODE, ' ') > 0 THEN 'Unspecified' ELSE sq.STATE_CODE END
    ) ELSE STATE.MASTER_CODE END AS STATE_CODE, 
    CASE WHEN COUNTRY.MASTER_CODE IS NULL THEN (
      CASE WHEN sq.COUNTRY_CODE IS NULL 
      OR LENGTH(sq.COUNTRY_CODE) = 0 
      OR REGEXP_INSTR(sq.COUNTRY_CODE, ' ') > 0 THEN 'Unspecified' ELSE sq.COUNTRY_CODE END
    ) ELSE COUNTRY.MASTER_VALUE END AS COUNTRY_CODE, 
    'Y' AS ACTIVE_FLG, 
    CREATED_BY.ROW_WID AS CREATED_BY_WID, 
    CHANGED_BY.ROW_WID AS CHANGED_BY_WID, -- 修正别名错误
    sq.CREATED_ON_DT, -- 新增缺失列
    sq.CHANGED_ON_DT, -- 新增缺失列
    sq.AUX1_CHANGED_ON_DT, 
    sq.AUX2_CHANGED_ON_DT, 
    sq.AUX3_CHANGED_ON_DT, 
    sq.AUX4_CHANGED_ON_DT, 
    sq.SRC_EFF_FROM_DT, 
    sq.SRC_EFF_TO_DT, 
    CASE WHEN W_BUSN_LOCATION_D.INTEGRATION_ID IS NULL THEN CURRENT_DATE ELSE W_BUSN_LOCATION_D.W_INSERT_DT END AS EFFECTIVE_FROM_DT, 
    TO_DATE('01013714', 'DDMMYYYY') AS EFFECTIVE_TO_DT, 
    'N' AS DELETE_FLG, 
    'Y' AS CURRENT_FLG, 
    CASE WHEN W_BUSN_LOCATION_D.INTEGRATION_ID IS NULL THEN CURRENT_DATE ELSE W_BUSN_LOCATION_D.W_INSERT_DT END AS W_INSERT_DT, 
    W_BUSN_LOCATION_D.W_UPDATE_DT AS W_UPDATE_DT, -- 修正别名错误
    sq.DATASOURCE_NUM_ID, 
    sq.INTEGRATION_ID, 
    CASE WHEN (
      SELECT 
        ETL_PROC_WID 
      FROM 
        TEST_RRX.DW_RRX.W_PARAM_G 
      WHERE 
        ROW_WID = 1
    ) = W_BUSN_LOCATION_D.ETL_PROC_WID THEN 'X' WHEN W_BUSN_LOCATION_D.INTEGRATION_ID IS NULL THEN 'I' WHEN W_BUSN_LOCATION_D.INTEGRATION_ID IS NOT NULL 
    AND (
      sq.CHANGED_ON_DT <> W_BUSN_LOCATION_D.CHANGED_ON_DT 
      OR sq.AUX1_CHANGED_ON_DT <> W_BUSN_LOCATION_D.AUX1_CHANGED_ON_DT 
      OR sq.AUX2_CHANGED_ON_DT <> W_BUSN_LOCATION_D.AUX2_CHANGED_ON_DT 
      OR sq.AUX3_CHANGED_ON_DT <> W_BUSN_LOCATION_D.AUX3_CHANGED_ON_DT 
      OR sq.AUX4_CHANGED_ON_DT <> W_BUSN_LOCATION_D.AUX4_CHANGED_ON_DT
    ) THEN 'U' END AS UPD_FLG 
  FROM 
    DW_RRX.W_BUSN_LOCATION_DS sq 
    LEFT OUTER JOIN CODE COUNTRY ON COUNTRY.SOURCE_CODE = sq.country_code 
    AND COUNTRY.DATASOURCE_NUM_ID = sq.DATASOURCE_NUM_ID 
    AND COUNTRY.CATEGORY = 'COUNTRY' 
    LEFT OUTER JOIN CODE CITY ON CITY.SOURCE_CODE_1 = sq.country_code 
    AND CITY.DATASOURCE_NUM_ID = sq.DATASOURCE_NUM_ID 
    AND CITY.SOURCE_CODE_2 = sq.STATE_CODE 
    AND CITY.SOURCE_NAME_1 = sq.CITY_NAME 
    AND CITY.CATEGORY = 'CITY' 
    LEFT OUTER JOIN CODE STATE ON STATE.DATASOURCE_NUM_ID = sq.DATASOURCE_NUM_ID 
    AND STATE.CATEGORY = 'STATE' 
    LEFT OUTER JOIN USER_TAB CREATED_BY ON sq.DATASOURCE_NUM_ID = CREATED_BY.DATASOURCE_NUM_ID 
    AND CREATED_BY.INTEGRATION_ID = sq.CREATED_BY_ID 
    AND CREATED_BY.EFFECTIVE_FROM_DT <= sq.CREATED_ON_DT 
    AND CREATED_BY.EFFECTIVE_TO_DT >= sq.CREATED_ON_DT 
    LEFT OUTER JOIN USER_TAB CHANGED_BY ON sq.DATASOURCE_NUM_ID = CHANGED_BY.DATASOURCE_NUM_ID 
    AND CHANGED_BY.INTEGRATION_ID = sq.CHANGED_BY_ID 
    AND CHANGED_BY.EFFECTIVE_FROM_DT <= sq.CHANGED_ON_DT 
    AND CHANGED_BY.EFFECTIVE_TO_DT >= sq.CHANGED_ON_DT 
    LEFT OUTER JOIN W_BUSN_LOCATION_D ON sq.INTEGRATION_ID = W_BUSN_LOCATION_D.INTEGRATION_ID 
    AND sq.DATASOURCE_NUM_ID = W_BUSN_LOCATION_D.DATASOURCE_NUM_ID
) 
SELECT 
  * 
FROM 
  BUSN_LOCATION 
WHERE 
  UPD_FLG <> 'X';

关键修复点说明 #

• 补充子查询中缺失的sq.CREATED_ON_DT、sq.CHANGED_ON_DT列，匹配视图声明的列列表
• 将CHANGED_BY.ROW_WID AS CREATED_BY_WID修正为CHANGED_BY.ROW_WID AS CHANGED_BY_WID，确保列名唯一且匹配视图定义
• 将W_BUSN_LOCATION_D.W_UPDATE_DT as current_date修正为W_BUSN_LOCATION_D.W_UPDATE_DT AS W_UPDATE_DT，避免使用内置函数作为别名，同时匹配视图列名
• 确保视图声明的每一列都能在子查询结果中找到对应名称的列，数量完全一致

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