ORA-01799 列不能外部联接到子查询

需求及问题

在一些表关联查询中，当存在源表和关联表都有过虑条件（and）时，如果其中一个条件不符合，结果就有可能为空；

而实际上我们要求结果集中，条件不符合的显示空，但其它条件正常的，依然要显示。

1.要达到目的，不符合的数据显示为空，符合的照常显示

2.直接在where里放入条件，当有一个不符合时，结果集可能为空

/*需求和问题*/
/*1.where中放两个查询条件,有可能数据集为空*/
selec distinct t t.sec_code, t.sec_sname, 
decode(t1.chng_pct,null,'--',TO_CHAR(t1.chng_pct,'FM9999999999999990.90')) chng_pct, t.trans_type, t.tradedate 
from mv_stk_trans_info t left outer join mv_sec_mkt t1 on t.sec_unicode = t1.sec_unicode 
where t.tradedate=(select max(tradedate) from mv_stk_trans_info ) 
and t1.tradedate=(select max(tradedate) from mv_stk_trans_info )
order by t.sec_code asc; 

3.把限制为空列的条件放到outer join的on中,要报ORA-01799:列不能外部联接到子查询的错

/*2.把限制为空列的条件放到outer join的on中,要报ORA-01799:列不能外部联接到子查询的错*/
select distinct t.sec_code, t.sec_sname, 
decode(t1.chng_pct,null,'--',TO_CHAR(t1.chng_pct,'FM9999999999999990.90')) chng_pct, t.trans_type, t.tradedate 
from mv_stk_trans_info t 
left outer join mv_sec_mkt t1 on t.sec_unicode = t1.sec_unicode 
and t1.tradedate=(select max(tradedate) from mv_stk_trans_info )
where t.tradedate=(select max(tradedate) from mv_stk_trans_info ) 
order by t.sec_code asc;

解决方案

1.先查询出关联表的关联列,条件列,以及其它需要的列,查询结果集作为一个表,再让其它表来关联这个结果集

/*(推荐)1.先查询出关联表的关联列,条件列,以及其它需要的列,查询结果集作为一个表,再让其它表来关联这个结果集*/
/*方案1,速度运行为0.938*/
select distinct t.sec_code, t.sec_sname, 
decode(t1.chng_pct,null,'--',TO_CHAR(t1.chng_pct,'FM9999999999999990.90')) chng_pct, t.trans_type, t.tradedate 
from mv_stk_trans_info t left outer join 
(select sec_unicode, chng_pct from mv_sec_mkt  
where tradedate=(select max(tradedate) from mv_sec_mkt)) t1 on t.sec_unicode=t1.sec_unicode 
where t.tradedate=(select max(tradedate) from mv_stk_trans_info )
order by t.sec_code asc; 

2.先用一个函数来完成关联表的限制条件,并返回限制条件,再在outer join的on中加入限制条件

/*2.先用一个函数来完成关联表的限制条件,并返回限制条件,再在outer join的on中加入限制条件*/
/*方案2,速度运行为3.922*/
--函数返回关联条件
create or replace function fun_getMaxDay return date as
  v_max_date date;
begin
  select max(tradedate) into v_max_date from mv_stk_trans_info;
  return v_max_date;
end;

--关联查询
select t.sec_code, t.sec_sname, 
decode(t1.chng_pct,null,'--',TO_CHAR(t1.chng_pct,'FM9999999999999990.90')) chng_pct, t.trans_type, t.tradedate 
from mv_stk_trans_info t left outer join mv_sec_mkt t1 on t.sec_unicode = t1.sec_unicode 
and t1.tradedate=fun_getMaxDay 
where t.tradedate=(select max(tradedate) from mv_stk_trans_info ) 
order by t.sec_code asc;

方案1.2均能达到目的，但方案1用时少，且方便，推荐1。