执行SQL时报invalid identifier s.site_id无效标识符错误排查

错误原因 #

• CTE sdv_ar未返回site_id与study_id字段：你在sdv_ar的查询逻辑中仅将brv.study_id与brv.site_id拼接为tu_alias_cd输出，没有单独返回这两个原始字段，因此在sdv_base中引用s.site_id、s.study_id时，数据库无法识别不存在的字段，抛出无效标识符错误。
• 存在重复关联的冗余逻辑：sdv_base中连续两次关联dm_ctm_tu表且使用了相同的别名tu，既没有业务意义，还会引发语法歧义。

修复方案 #

因为你已经在sdv_ar中提前拼接好了关联用的tu_alias_cd字段，直接删除冗余的关联逻辑、用已有的字段做关联即可，改动量最小，修改后的sdv_base代码如下：

sdv_base AS (
    SELECT
        trc.geo_reg1_nm     AS region,
        trc.ropu_nm         AS ropu,
        trc.ctry_nm         AS country,
        trc.trc_alias_cd    AS study_country,
        trc.ctry_nm         AS tr_ctry,
        tu.tu_alias_cd      AS study_site,
        tu.tu_status,
        s.record_id,
        s.entered_dt,
        s.visit_dt,
        s.subject_number
    FROM
        sdv_ar s 
        INNER JOIN dm_ctm_tu tu 
            ON s.tu_alias_cd = tu.tu_alias_cd
            AND tu.curr_flg = 1
        INNER JOIN dm_ctm_trc trc 
            ON trc.ctry_cd = tu.ctry_cd
            AND trc.tr_no = tu.tr_no
            AND trc.curr_flg = 1
--TEMPORAL_COMMENT_UNTIL_DATA-- INNER JOIN dm_ctm_tr tr ON tr.tr_no=trc.tr_no AND tr.curr_flg=1 AND tr.tr_stat < 4950 /* Excluding ended studies */
)

可选调整 #

如果后续业务逻辑需要单独使用study_id或site_id字段，只需要在sdv_ar的内层SELECT查询中补充这两个字段的返回即可：

SELECT
    brv.subject_uid,
    brv.record_id,
    brv.log_id,
    brv.audit_subcat,
    brv.study_event_id,
    brv.int_tec_fr_dt,
    brv.subject_id AS subject_number,
    brv.study_id, -- 新增返回study_id
    brv.site_id, -- 新增返回site_id
    brv.study_id || '-' || brv.site_id AS tu_alias_cd,
    -- 剩余原有查询逻辑保持不变

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