我在嵌套查询上收到错误。我尝试通过给它一个别名并在子查询中调用它来从父查询中选择 id。如何从 mytable 外部查询中获取 id?
SELECT * FROM mytable t
WHERE (
SELECT count(*) number_of_weekdays FROM
(
SELECT start_date, to_char(start_date+(level-1), 'fmday') d_day
FROM ( select strt_dt as start_date , trunc(sysdate) as end_date from mytable where id = t.id )
connect by level <= end_date - start_date + 2
)
WHERE d_day IN (
select rtrim(ltrim(regexp_substr(intv,'[^,]+', 1, level))) from ( select intv from mytable where id = t.id )
connect by regexp_substr(intv, '[^,]+', 1, level) is not null
)
) <= col1
专栏 I 主要关注:
INTV | STRT_DT
--------------------------------------------------
3, monday, tuesday, thursday, sunday | 07-MAY-18
错误:
ORA-00904: "T"."ID": invalid identifier
00904. 00000 - "%s: invalid identifier"
从子查询中,您只能引用直接外部查询的数据。不能跨多个级别的查询和子查询访问数据。
在您的情况下,您有三个级别的子查询,即总共四个级别,并且您尝试从最内层访问最外层查询的数据。