outer join operator (+) not allowed in operand of OR or IN

[getjbb]

Hi,

I am getting this error message when trying to run a select.
The error is coming from the following where clause:

WHERE
LOAN.loan_id = EOM.loan_id AND
CONTCT.loan_id = LOAN.loan_id AND
LOAN.dealer_id = TITLER.title_id AND
EOM.loan_id = NA.loan_id(+) AND
EOM.eom_date = NA.accrual_date (+) AND
EOM.loan_id = EOM2.loan_id AND
EOM.status like 'BK%' and EOM.status not like 'BKR%' AND
EOM2.status like 'BK%' and EOM2.status not
like 'BKR%'AND
EOM.balance > 0 AND
EOM.loan_balance > 0 AND
(((EOM.eom_date = TO_DATE('09/30/2005', 'MM/DD/YYYY'))
AND (EOM2.eom_date = ADD_MONTHS(TO_DATE('09/30/2005', 'MM/DD/YYYY'), -1))) AND
((EOM.contr_delinq_days >= 61) AND (EOM.contr_delinq_days <= 120))) OR
(EOM.contr_delinq_days >= 121);

I think the statement in bold is giving me the problem, but I am not sure about how to fix it.

The bolded area should be:

( 2 consecutive months with a BK% status code AND (contractual delinquency days >= 61 and contractual delinquency days <= 120 )

OR

contractual delinquency >= 121 days

If anyone could help me I would appreciate it.

getjbb

 

[BJCooperIT]

What error message are you getting?

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[BJCooperIT]

Sorry, do you mean the title is the error message? Which statement is flagged? More than just the WHERE clause would be helpful.



^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: Emu Products Plus

 

[SantaMufasa]

Yes, JBB, here is the scenario that is occurring with your current code:

WHERE        
    LOAN.loan_id = EOM.loan_id AND 
    CONTCT.loan_id = LOAN.loan_id AND 
    LOAN.dealer_id = TITLER.title_id AND 
    EOM.loan_id = NA.loan_id(+) AND
    EOM.eom_date = NA.accrual_date (+) AND
    EOM.loan_id = EOM2.loan_id AND
    EOM.status like 'BK%' and EOM.status not like 'BKR%' AND
    EOM2.status like 'BK%' and EOM2.status not 
         like 'BKR%'AND
    EOM.balance > 0 AND
    EOM.loan_balance > 0 AND
    (((EOM.eom_date = TO_DATE('09/30/2005', 'MM/DD/YYYY'))
       AND (EOM2.eom_date
         = ADD_MONTHS(TO_DATE('09/30/2005', 'MM/DD/YYYY'), -1)))
       AND
      ((EOM.contr_delinq_days >= 61) AND (EOM.contr_delinq_days <= 120))) OR 
       (EOM.contr_delinq_days >= 121);

    EOM.eom_date = NA.accrual_date (+) AND
                 *
ERROR at line nn:
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

A way that you can "fool" Oracle is to code at least the "OR" portion of your WHERE clause as the result of a user-defined function:

Section 1 -- a user-defined function to evaluate your (non-outer-join) conditions:

CREATE OR REPLACE FUNCTION check_delinq
    (eom_date1 date,eom_date2 date, contr_delinq_days number)
    return char
is
    compare_date    date := TO_DATE('09/30/2005','MM/DD/YYYY');
begin
    if  (((   eom_date1 = compare_date)
          and eom_date2 = add_months(compare_date,-1)
         )
         and
         (    (contr_delinq_days >= 61)
          and (contr_delinq_days <= 120)
         ))
        OR (contr_delinq_days >= 121) then
        return 'Y';
    else
        return 'N';
    end if;
end;
/

Function created.

Section 2 -- Invocation of the function to achieve your objective:

WHERE        
    LOAN.loan_id = EOM.loan_id AND 
    CONTCT.loan_id = LOAN.loan_id AND 
    LOAN.dealer_id = TITLER.title_id AND 
    EOM.loan_id = NA.loan_id(+) AND
    EOM.eom_date = NA.accrual_date (+) AND
    EOM.loan_id = EOM2.loan_id AND
    EOM.status like 'BK%' and EOM.status not like 'BKR%' AND
    EOM2.status like 'BK%' and EOM2.status not 
         like 'BKR%'AND
    EOM.balance > 0 AND
    EOM.loan_balance > 0 AND[b]
    check_delinq
        (EOM.eom_date
        ,EOM2.eom_date
        ,EOM.contr_delinq_days) = 'Y';[/b]

Try this out and let us know your satisfaction with this resolution.


Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SimonSellick]

I also had this problem and worked around it by using a sub-query to implement the OR condition.

BTW, Oracle documentation recommends the ANSI JOIN syntax and states that doing so removes the restriction on using an outer join column in an IN or OR condition - but it doesn't always work - see

http://www.tek-tips.com/viewthread.cfm?qid=1136438&page=1.

 

[getjbb]

SantaMufasa,

I do not understand why the result from the function would return a 'Y' which include both criterias:

***A***((( eom_date1 = compare_date)
and eom_date2 = add_months(compare_date,-1)
)
and
( (contr_delinq_days >= 61)
and (contr_delinq_days <= 120)
))

--- OR ---

***B***OR (contr_delinq_days >= 121) then
return 'Y';

The result should be A or B, not both. It seem to me that both conditions are being taken into account.

Again, if:
((( eom_date1 = compare_date)
and eom_date2 = add_months(compare_date,-1)
)
and
( (contr_delinq_days >= 61)
and (contr_delinq_days <= 120)
))

is true it should be included as part of the
criteria to select the result set else the
following criteria should be include instead,
not both:

(contr_delinq_days >= 121)

Maybe I am not understanding what you are doing.
Whatever help you give me is greatly appreciated.



getjbb

 

[getjbb]

BJCooperIT,

Yes, the title is the error. The text in bold is being flagged. The only othe part of the statement is the select clause which is selecting a number of columns. I did not think that was needed since the problem is the where clause.

getjbb

 

[SantaMufasa]

JBB,

You want to see rows,

"WHERE
LOAN.loan_id = EOM.loan_id AND
CONTCT.loan_id = LOAN.loan_id AND
LOAN.dealer_id = TITLER.title_id AND
EOM.loan_id = NA.loan_id(+) AND
EOM.eom_date = NA.accrual_date (+) AND
EOM.loan_id = EOM2.loan_id AND
EOM.status like 'BK%' and EOM.status not like 'BKR%' AND
EOM2.status like 'BK%' and EOM2.status not
like 'BKR%'AND
EOM.balance > 0 AND
EOM.loan_balance > 0
AND
(*** A ***
OR
*** B ***")

My user-defined function does just that...it checks to see
[tt]
"IF *** A *** (is true) OR
*** B *** (is true), then return 'Y' "[/tt]
(certainly BOTH cannot be true at the same time)

IF NEITHER is TRUE, then you get "N" returned...
...which is exactly what you want.





Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[getjbb]

SantaMufasa,

Thank you so much for the explaination. I will try it out and see what results I get.

getjbb