ORA-01799: 열을 하위 쿼리에 외부 결합할 수 없습니다.

ORA-01799: 열을 하위 쿼리에 외부 결합할 수 없습니다.

여기 제 질문이 있습니다.

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

그리고 여기 제가 받고 있는 오류 메시지가 있습니다.

내가 뭘 잘못하고 있는 거지?

하위 쿼리를 눌러 외부 결합되지 않도록 다시 작성할 수 있습니다.

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 ) 

Oracle은 외부 조인에 대한 조인 조건 내 하위 쿼리 사용을 지원하지 않습니다.그래서 당신은 하위 질의를 제거해야 합니다.

문제는, 그것이 왜 거기에 있는가 하는 것입니다.두 곳에 "<=" 조건이 있으므로, 술어에는 기본적으로 "시행일이 지금보다 늦지 않은 최신 시행일인 모든 레코드"가 표시됩니다.만약 그것이 당신이 정말로 원하는 것이라면, 당신은 그것을 "시행일이 늦어도 지금까지인 모든 기록", 즉, 다음과 같이 단순화할 수 있습니다.

ON 
    (C.SETID = CO.SETID 
        AND C.CUST_ID = CO.CUST_ID 
        AND CO.effdt <= SYSDATE    
)

Voila, 세부사항은 없습니다.

하지만 그것이 정말 당신이 원하는 것입니까, 아니면 당신은 첫 번째 "<="가 단지 "="이 된다는 것을 의미합니까? 즉, 지금까지 가장 최근에 유효한 날짜가 있는 기록을 찾는 것입니까?당신이 정말로 원하는 것이 그것이라면, 다시 쓰는 것은 더 복잡할 것입니다.

옵션 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;

옵션 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'))

옵션 1은 "오늘"(COI) 기준으로 MYCUSTOPTION 테이블을 쿼리하기만 하면 작동합니다.EFFDT <= SYSDATE). 이 경우 당신의 요구 사항으로 보입니다.그러나 "C" 기준으로 MYCUSTOPTION을 조회하려면 작동하지 않습니다.SIR_DT"

옵션 2는 약간 복잡해 보이지만 더 잘 작동하므로 다른 COI(시스템 날짜 또는 다른 날짜 필드)를 변경하지 않고 sysdate 또는 다른 날짜 필드 간에 전환할 수 있습니다.EFFDT < = C.SIR_DT)

질문에 대한 답변은 이미 완료되었지만, 일정한 날짜가 아닌 열을 기준으로 최신 EFFDT를 받아야 하는 경우가 약간 다를 수 있습니다.그런 경우에는 불완전한 선택지와 추악한 해결책을 하나 발견했을 뿐입니다

불완전한 옵션:

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

CUST_OPT 테이블에 미래 날짜가 있지만 현재 날짜가 없는 경우(<=N) 불완전한 옵션입니다.ISSU_DT) 날짜: 외부 조인이 작동하지 않고 행이 반환되지 않습니다.일반적으로 PeopleSoft 용어(네, 거기서 SETID+EFFDT를 보았습니다!;-D)에서는 사람들이 "영원히" 이후 첫 번째 값을 효과적으로 만들기 위해 EFFDT를 만드는 경향이 있기 때문에 이러한 일이 자주 발생하지는 않지만 항상 그런 것은 아니기 때문에 다음과 같은 추악한 해결책도 있습니다.

저는 또한 다음과 같은 추악한 옵션을 찾았습니다(그러나 실제로는 권장합니다. 문제를 해결할 수 있으므로 해결책이라고 부릅니다).

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

이 옵션은 무시해야 하는 이후 행을 반환합니다!따라서 SELECT 문에 조건을 추가하여 반환된 값을 검색할 의도가 아닌 경우 무시합니다.내가 말했듯이...그것은 추악한 해결책이지만, 해결책입니다.

내 추악한 솔루션의 경우, 행이 나중에 Application Engine이나 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

나는 또한 오늘 이 문제에 직면했고 생각해 냈습니다.

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

그것들을 시도한 후에.LEFT OUTER JOIN다른 대답들로부터, 그것은 효과가 있지만, 그것은 정말 느립니다.

나만의 최선의 해결책은 당신이 필요로 하는 최신 레코드와 관련된 VIEW를 만들고 그에 대해 왼쪽 조인을 수행하는 것입니다.

CREATE VIEW VIEW_SOF_TOF as
SELECT SOF."FIELDA1", TOF."FIELDB1", TOF."FIELDB2", ...
FROM SOF, TOF
WHERE FIELDA1 (+) = FIELDB1
  AND ( FIELDB2 = (SELECT MAX(FIELDB2) FROM TOF WHERE FIELDA1 = FIELDB1)
    OR FIELDB2 IS NULL)

언급URL : https://stackoverflow.com/questions/14571254/ora-01799-a-column-may-not-be-outer-joined-to-a-subquery