oracle ORA-01799: 列不能外连接到子查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14571254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01799: a column may not be outer-joined to a subquery
提问by dotnet-practitioner
Here is my query
这是我的查询
SELECT
COUNT(C.SETID)
FROM
MYCUSTOMER C
LEFT OUTER JOIN MYCUSTOPTION CO
ON
(C.SETID = CO.SETID
AND C.CUST_ID = CO.CUST_ID
AND CO.effdt = (
SELECT MAX(COI.EFFDT)
FROM MYCUSTOPTION COI
WHERE
COI.SETID = CO.SETID
AND COI.CUST_ID = CO.CUST_ID
AND COI.EFFDT <=SYSDATE
)
)
and here is the error message that I am getting..
这是我收到的错误消息..
What am I doing wrong???
我究竟做错了什么？？？
回答by DazzaL
you can rewrite that by pushing the sub query so that its not outer joined:
您可以通过推送子查询来重写它，使其不是外部连接：
select Count(C.setid)
from mycustomer C
left outer join (select *
from mycustoption co
where co.effdt <= (select Max(COI.effdt)
from mycustoption COI
where COI.setid = co.setid
and COI.cust_id = co.cust_id
and COI.effdt <= sysdate)) co
on ( C.setid = CO.setid
and C.cust_id = CO.cust_id )
回答by Dave Costa
Well, Oracle apparently doesn't support using a subquery inside the join condition for an outer join. So you need to get rid of the subquery.
好吧，Oracle 显然不支持在外部连接的连接条件中使用子查询。所以你需要摆脱子查询。
The question is, why is it there at all? You have "<=" conditions in two places, so the predicate essentially says "all records whose effective date is no later than the latest effective date that is no later than now". If that's what you really want, you could simplify it to "all records whose effective date is no later than now", i.e.:
问题是，它为什么会在那里？你在两个地方有“<=”条件，所以谓词本质上说“所有有效日期不晚于不晚于现在的最新生效日期的记录”。如果这是您真正想要的，您可以将其简化为“生效日期不晚于现在的所有记录”，即：
ON
(C.SETID = CO.SETID
AND C.CUST_ID = CO.CUST_ID
AND CO.effdt <= SYSDATE
)
Voila, no subquery.
瞧，没有子查询。
But is that really what you want, or did you mean that first "<=" to be just "=" -- i.e. find the record with the most recent effective date before now? If that's what you really want, it will be more complex to rewrite.
但这真的是你想要的，还是你的意思是第一个“<=”只是“=”——即找到最近生效日期之前的记录？如果那是你真正想要的，重写会更复杂。
回答by Aqib Butt
i also faced this issue today and comeup with
我今天也遇到了这个问题并想出了
SELECT
COUNT(C.SETID)
FROM
MYCUSTOMER C
LEFT OUTER JOIN MYCUSTOPTION CO
ON
(C.SETID = CO.SETID
AND C.CUST_ID = CO.CUST_ID
AND CO.effdt IN (
SELECT MAX(COI.EFFDT)
FROM MYCUSTOPTION COI
WHERE
COI.SETID = CO.SETID
AND COI.CUST_ID = CO.CUST_ID
AND COI.EFFDT <=SYSDATE
)
)
回答by rishabh agnihotri
Option 1
选项1
select COUNT(C.SETID)
from MYCUSTOMER C
left outer join (
select *
from MYCUSTOPTION CO
on CO.effdt = (
select MAX(COI.EFFDT)
from MYCUSTOPTION COI
where COI.SETID = CO.SETID
and COI.CUST_ID = CO.CUST_ID
and COI.EFFDT <= SYSDATE
)
) CO
on C.SETID = CO.SETID
and C.CUST_ID = CO.CUST_ID;
Option 2
选项 2
select COUNT(C.SETID)
from MYCUSTOMER C
left outer join MYCUSTOPTION CO
on C.SETID = CO.SETID
and C.CUST_ID = CO.CUST_ID
where nvl(CO.effdt, to_date('19000101', 'YYYYMMDD')) = NVL((
select MAX(COI.EFFDT)
from MYCUSTOPTION COI
where COI.SETID = CO.SETID
and COI.CUST_ID = CO.CUST_ID
and COI.EFFDT <= C.SINCE_DT
), to_date('19000101', 'YYYYMMDD'))
Option1 works fine as long as you want to query MYCUSTOPTION table as of "today"(COI.EFFDT <= SYSDATE) which appears to be your req in this case. But it won't work if you want to query MYCUSTOPTION as of "C.SINCE_DT"
只要您想查询“今天”（COI.EFFDT <= SYSDATE）的 MYCUSTOPTION 表，Option1 就可以正常工作，这在这种情况下似乎是您的要求。但是如果你想从“C.SINCE_DT”查询 MYCUSTOPTION 就行不通了
Option2 looks bit complex but it works better, allows you to switch between sysdate or any other date field without changing anything else(COI.EFFDT <= C.SINCE_DT)
Option2 看起来有点复杂，但效果更好，允许您在 sysdate 或任何其他日期字段之间切换而无需更改任何其他内容（COI.EFFDT <= C.SINCE_DT）
回答by LFLFM
Your question has already been answered, but someone might have a slightly different case where they need to get the latest EFFDT based on a column, instead of a fixed date. For those cases, I only found one IMPERFECT option, and one UGLY solution...
您的问题已经得到解答，但有些人可能会有稍微不同的情况，他们需要根据列而不是固定日期获取最新的 EFFDT。对于这些情况，我只找到了一个不完美的选项和一个丑陋的解决方案......
Imperfect option:
不完美的选择：
SELECT ...
FROM MYTABLE N, CUST_OPT C
WHERE etc...
AND C.SETID (+) = N.SETID
AND C.CUST_ID (+) = N.CUST_ID
AND NVL(C.EFFDT,TO_DATE('01011900','DDMMYYYY')) = NVL((SELECT MAX(EFFDT)
FROM CUST_OPT SC
WHERE SC.SETID = C.SETID
AND SC.CUST_ID = C.CUST_ID
AND SC.EFFDT <= N.ISSUE_DT)
,TO_DATE('01011900','DDMMYYYY'))
It is an imperfect option because if the CUST_OPT table has future dates, but no current (<=N.ISSUE_DT) dates, the outer join will not work and no rows will be returned. In general PeopleSoft terms (yes I saw your SETID+EFFDT there! ;-D) this wouldn't happen very often as people tend to create one 01/01/1900 EFFDT to make a first value effective since "forever", but since it's not always the case; we also have an ugly solution:
这是一个不完美的选择，因为如果 CUST_OPT 表有未来日期，但没有当前 (<=N.ISSUE_DT) 日期，则外连接将不起作用并且不会返回任何行。一般来说，PeopleSoft 术语（是的，我在那里看到了您的 SETID+EFFDT！;-D）这不会经常发生，因为人们倾向于创建一个 01/01/1900 EFFDT 来使第一个值自“永远”生效，但由于情况并非总是如此；我们还有一个丑陋的解决方案：
I also found one UGLY option (but I actually recommend it, and it solves the problem, so let's call it a solution), which is this:
我还发现了一个丑陋的选项（但我实际上推荐它，它解决了问题，所以我们称之为解决方案），它是这样的：
SELECT n.field1, n.field2,
CASE WHEN NVL(c.EFFDT,n.ISSUE_DT-1)<=n.ISSUE_DT THEN c.field1 ELSE NULL END,
CASE WHEN NVL(c.EFFDT,n.ISSUE_DT-1)<=n.ISSUE_DT THEN c.field2 ELSE NULL END
FROM MYTABLE N, CUST_OPT C
WHERE etc...
AND C.SETID (+) = N.SETID
AND C.CUST_ID (+) = N.CUST_ID
AND NVL(C.EFFDT,TO_DATE('01011900','DDMMYYYY')) = NVL((SELECT MAX(EFFDT)
FROM CUST_OPT SC
WHERE SC.SETID = C.SETID
AND SC.CUST_ID = C.CUST_ID
AND SC.EFFDT <= N.ISSUE_DT)
,NVL( (SELECT MIN(EFFDT)
FROM CUST_OPT SC
WHERE SC.SETID = C.SETID
AND SC.CUST_ID = C.CUST_ID
AND SC.EFFDT >= N.ISSUE_DT)
,TO_DATE('01011900','DDMMYYYY')
)
)
This option WILL return FUTURE rows which must be ignored! So we add the conditions on the SELECT statement that will IGNORE the returned values, if they weren't meant to be retrieved. Like I said... it's an UGLY solution, but it is a solution.
此选项将返回必须忽略的 FUTURE 行！因此，我们在 SELECT 语句上添加条件，如果它们不打算检索，则将忽略返回的值。就像我说的......这是一个丑陋的解决方案，但它是一个解决方案。
For my ugly solution, if the rows will be processed later in an Application Engine or PL/SQL or whatever; you can, instead of having a CASE statement for each column, just add a new column that will tell you that you fetched "improper" data and ignore the fields later in your code, based on this column, like this:
对于我丑陋的解决方案，如果这些行稍后将在应用程序引擎或 PL/SQL 或其他任何东西中处理；您可以，而不是每列都有一个 CASE 语句，只需添加一个新列，该列会告诉您您提取了“不正确”的数据，并根据此列在代码中稍后忽略这些字段，如下所示：
CASE WHEN NVL(c.EFFDT,n.ISSUE_DT-1)<=n.ISSUE_DT THEN 'N' ELSE 'Y' END AS IGNORE_CUST_OP_COLS
