oracle JOIN 问题：更正要解决的 SQL 语句：ORA-01799：列可能无法外部连接到子查询

JOIN Issue : Correct the SQL Statement to solve : ORA-01799: a column may not be outer-joined to a subquery

提问by Bilgin K?l??

As you see below; how can I implement fx.ftf_validitystartdate= ... this lines value since oracle does not allow me to do it like this below .

正如你在下面看到的；我怎样才能实现 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
    )

回答by Gordon Linoff

It should work if you switch this to a whereclause:

如果您将其切换为where子句，它应该可以工作：

select *
from acc_accounts acc join
     kp_paramcore p
     on acc.account_no = p.accountnum and
        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'
 where fx.ftf_validitystartdate= (SELECT MAX(ff.ftf_validitystartdate)
                                 FROM ftf_rates ff
                                 WHERE ff.status = 'A' and
                                       ff.statusrec = 'A'
                                       p.v_CurrentDate BETWEEN ff.systemstartdate AND ff.systemfinishdate                                            AND ff.currencycode = acc.currencsw_kod
                                )

However, you lose the 'left outer join' characteristics, so you would also want to add: or fx.ftf_validitystartdate is null. I guess that v_CurrentDate comes from "p". It is always a good idea to use table aliases before column names.

但是，您失去了“左外连接”特性，因此您还需要添加： or fx.ftf_validitystartdate is null。我猜 v_CurrentDate 来自“p”。在列名之前使用表别名总是一个好主意。

However, I question whether the subquery is really needed. It is only needed when there is more than one record that meets the conditions inside the subquery. Otherwise, I think you can just change the onclause to be:

但是，我质疑是否真的需要子查询。仅当子查询内满足条件的记录不止一条时才需要。否则，我认为您可以将on条款更改为：

    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
       p.v_CurrentDate BETWEEN fx.systemstartdate AND fx.systemfinishdate

回答by dani herrera

I publish the workaround with CTE and tested only in Oracle 11g.

我使用 CTE 发布了解决方法，并且仅在 Oracle 11g 中进行了测试。

To make test I create this schema:

为了进行测试，我创建了这个架构：

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);

At this time I force error with this query:

此时我强制使用此查询出错：

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);

And now I rewrite query with CTE:

现在我用 CTE 重写查询：

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;

This second query returns right results.

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

I rewrite your query with CTE:

我用 CTE 重写您的查询：

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' 

Notice, only tested in Oracle 11g. See @a_horse_with_no_name coment:

注意，仅在 Oracle 11g 中测试。见@a_horse_with_no_name 评论：

@danihp: CTEs were available long before Oracle 11g (I think they were introducted in 9.1 maybe even earlier - but they are definitely available in 10.x). 11.2 introduced recursive CTEs which is not needed in this case. –

@danihp：CTE 早在 Oracle 11g 之前就可用了（我认为它们在 9.1 中可能更早引入 - 但它们肯定在 10.x 中可用）。11.2 引入了在这种情况下不需要的递归 CTE。——