In the example SQL below, the base_dt column used in the first subquery becomes an outer join, and in the subquery
I understand that it is correct to spit out an error because the data format is incorrect.
If you perform the union all part of the subquery as a part, both 12c and 19c will result in error.
When executing the entire SQL, 12c is executed normally, but 19c error is spit out.
Is this a bug in Oracle 12c, or is it possible to adjust the hidden parameters so that the test can be performed in 19c?
with temp1 as(
select '01' as gubun, 100 as amt, 5 as cnt from dual union all
select '02' as gubun, 300 as amt, 3 as cnt from dual union all
select '03' as gubun, 500 as amt, 1 as cnt from dual union all
select '04' as gubun, 600 as amt, 2 as cnt from dual union all
select '05' as gubun, 700 as amt, 4 as cnt from dual
)
select t.gubun
, sum(t.amt) as tot_amt
, sum(t.tot_cnt) as tot_cnt1
from (
selelect t1.gubun
, t1.amt
, t1.cnt + t2.cnt as tot_cnt
from temp1 t1
, ( select '01' as gubun, 1 as cnt, sysdate as base_dt from dual
union all select '02' as gubun, 7 as cnt, '20240424' as base_dt from dual
union all select '03' as gubun, 3 as cnt, to_char(sysdate,'YYYYMMDD') as base_dt from dual
) t2
where t1.gubun = t2.gubun(+)
and t2.base_dt(+) >= '20240421'
and t2.base_dt(+) <= '20240428'
union all
selelect t1.gubun
, t1.amt
, t1.cnt + t2.cnt as tot_cnt
from temp1 t1
, ( select '04' as gubun, 1 as cnt, to_char(sysdate,'YYYYMMDD') as base_dt from dual
union all select '02' as gubun, 1 as cnt, '20240422' as base_dt from dual
) t2
where t1.gubun = t2.gubun(+)
) t
group by t.gubun