[oracle]联接问题︰ 正确的 SQL 语句,以解决︰ ORA 01799: 列可能不是外部连接到子查询

标签: sql Oracle
发布时间: 2017/4/15 18:49:29
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

正如你看到的下面;如何可以实现 fx.ftf_validitystartdate=......这线值,因为 oracle 不允许我,就像下面这样做。

    select * from  acc_accounts acc
    join kp_paramcore p on
    acc.account_no = p.accountnum
    acc.suffix = p.suffixc
         LEFT JOIN ftf_rates fx
              ON p.maturestart = fx.ftf_vadealtsinir
             AND p.maturefinish = fx.ftf_vadeustsinir
             AND fx.statusrec = 'A'
             AND fx.currencycode = acc.currencsw_kod
             AND fx.status= 'A' 
  and  fx.ftf_validitystartdate= (SELECT MAX(ff.ftf_validitystartdate)
                                               FROM ftf_rates ff
                                              WHERE ff.status = 'A'
                                                AND ff.statusrec = 'A'
                                                AND v_CurrentDate BETWEEN ff.systemstartdate AND ff.systemfinishdate                                            AND ff.currencycode = acc.currencsw_kod
    )

解决方法 1:

我发布的替代与热膨胀系数的方法和测试只在 Oracle 11g 中。

若要使测试我创建此架构

create table t_a ( a int );
create table t_b ( a int);
create table t_c ( a int);

insert into t_a values (1);
insert into t_a values (2);
insert into t_a values (3);

insert into t_b values (1);
insert into t_b values (2);
insert into t_b values (3);

insert into t_c values (1);
insert into t_c values (2);
insert into t_c values (3);

在这个时候,我强迫与此查询错误︰

select * 
from t_a
left outer join t_b
  on t_a.a = t_b.a and
     t_b.a = ( select max( a )
             from t_c);

现在我重写与热膨胀系数的查询︰

with cte (a ) as (
   select a
   from t_b
   where t_b.a = ( select min( a )
             from t_c)
)
select * 
from t_a
left outer join cte
  on t_a.a = cte.a;

这第二个查询返回正确的结果。

我重写与热膨胀系数的查询︰

with CTE as (
   select * from ftf_rates 
   where ftf_validitystartdate= (SELECT MAX(ff.ftf_validitystartdate)
                                 FROM ftf_rates ff
                                 WHERE ff.status = 'A'
                                      AND ff.statusrec = 'A'
                                      AND v_CurrentDate BETWEEN ff.systemstartdate 
                                      AND ff.systemfinishdate                   
                                      AND ff.currencycode = acc.currencsw_kod )

)
    select * from  acc_accounts acc
    join kp_paramcore p on
    acc.account_no = p.accountnum
    acc.suffix = p.suffixc
         LEFT JOIN CTE fx
              ON p.maturestart = fx.ftf_vadealtsinir
             AND p.maturefinish = fx.ftf_vadeustsinir
             AND fx.statusrec = 'A'
             AND fx.currencycode = acc.currencsw_kod
             AND fx.status= 'A' 

请注意,只是在 Oracle 11g 测试。请参阅 @a_horse_with_no_name 水泥︰

@danihp: Cte 是很久以前 Oracle 11g (我认为他们是介绍在 9.1 也许更早 — — 但它们是肯定可用在 10.x) 可用。11.2 介绍了递归 Cte 不在这种情况下的需要。–

官方微信
官方QQ群
31647020