为何无法在WHERE子句中使用子查询因子化（WITH子句）结果？

你遇到这个错误的核心原因很明确：你定义的CTE（也就是WITH子句里的rpt）根本没和主查询建立关联。

虽然你用WITH子句创建了rpt这个临时结果集，但主查询的SELECT语句里，既没有把rpt加到FROM/JOIN列表里，也没有通过子查询的方式引用它——Oracle在解析WHERE rt.reportid = rpt.id的时候，根本找不到rpt这个对象，自然会报错说标识符无效。

给你两个可行的修正方案：

方案一：把rpt加入主查询的JOIN关联 #

直接在主查询里新增和rpt的连接，让Oracle能识别到rpt的存在：

WITH rpt AS(
    -- 先修正rownum和order by的顺序问题，确保拿到最新的记录
    SELECT * FROM (
        SELECT * FROM reports WHERE caseid = :case_id ORDER BY created DESC
    ) WHERE rownum=1
)
SELECT DISTINCT rt.trialid 
FROM report_trials rt
JOIN trial_genes tg ON rt.id=tg.trialid
JOIN rpt ON rt.reportid = rpt.id -- 新增与rpt的连接
WHERE tg.gene NOT IN('TMB','MS')

方案二：用标量子查询直接获取rpt.id的值 #

因为你的rpt只返回一行记录（rownum=1），所以可以直接在WHERE子句里用标量子查询替代CTE，这样更简洁：

SELECT DISTINCT rt.trialid 
FROM report_trials rt
JOIN trial_genes tg ON rt.id=tg.trialid
WHERE rt.reportid = (
    -- 先排序再取rownum，确保拿到最新的reports记录
    SELECT id FROM (
        SELECT * FROM reports WHERE caseid = :case_id ORDER BY created DESC
    ) WHERE rownum=1
) AND tg.gene NOT IN('TMB','MS')

另外要提醒你：你原来的rpt定义里，rownum=1写在ORDER BY前面是错误的！Oracle会先筛选rownum=1的记录，再执行排序，这会导致你拿到的可能不是最新创建的那条数据。必须先把排序后的结果作为子查询，再在外层加rownum=1的筛选，才能保证拿到正确的最新记录。

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