OraFAQ Forum: SQL & PL/SQL » ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01719: outer join operator (+) not allowed in operand of OR or IN [message #29743]

Fri, 19 March 2004 05:49

Ned
Messages: 7
Registered: December 2001

Junior Member

Hi,

I am using Orcale 8.1.7� on Windows 2000.

I got the ORA-01719 error message and tried to apply the solution done in the Oracle documentation but�there seems� I am not getting the correct answer even I don't have the error message.

Here the Where clause

WHERE COSTED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND COSTED.TOTALCOSTEDTIME IS NOT NULL
OR BILLABLE.TOTALBILLABLETIME IS NOT NULL
OR UNBILLED.TOTALUNBILLEDTIME IS NOT NULL
OR ALLTIME.TOTALTIME IS NOT NULL;

In the documentation It said

/*ORA-01719: outer join operator (+) not allowed in operand of OR or IN

Cause: An outer join appears in an OR clause.

Action: If A and B are predicates, to get the effect of (A(+) OR B), try using the following:

(SELECT WHERE (A+ AND NOT B)) UNION ALL (SELECT WHERE (B)); */

So I take�

COSTED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND COSTED.TOTALCOSTEDTIME IS NOT NULL

As A+

and

OR BILLABLE.TOTALBILLABLETIME IS NOT NULL
OR UNBILLED.TOTALUNBILLEDTIME IS NOT NULL
OR ALLTIME.TOTALTIME IS NOT NULL

As B

�

And my query was changed as

SELECT� .....

FROM ......

WHERE COSTED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND COSTED.TOTALCOSTEDTIME IS NOT NULL
AND� NOT (BILLABLE.TOTALBILLABLETIME IS NOT NULL
OR UNBILLED.TOTALUNBILLEDTIME IS NOT NULL
OR ALLTIME.TOTALTIME IS NOT NULL)

UNION ALL

SELECT� .....

FROM ......

WHERE�BILLABLE.TOTALBILLABLETIME IS NOT NULL
OR UNBILLED.TOTALUNBILLEDTIME IS NOT NULL
OR ALLTIME.TOTALTIME IS NOT NULL

Any help

�

Thanks

�

�

�

�

�

Report message to a moderator

Re: ORA-01719: outer join operator (+) not allowed in operand of OR or IN [message #29744 is a reply to message #29743]

Fri, 19 March 2004 06:15

sverch
Messages: 582
Registered: December 2000

Senior Member

Perhaps you do not need UNION ALL, just UNION that can effectively be used instead of OR:

WHERE COSTED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND COSTED.TOTALCOSTEDTIME IS NOT NULL
UNION

...
WHERE COSTED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.TOTALBILLABLETIME IS NOT NULL
UNION
....
WHERE COSTED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.TOTALUNBILLEDTIME IS NOT NULL
UNION
...
WHERE COSTED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND BILLABLE.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND UNBILLED.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.WORKTYPEID (+) = TWORKTYPE.UNIQUEID
AND ALLTIME.TOTALTIME IS NOT NULL;

Report message to a moderator

Re: ORA-01719: outer join operator (+) not allowed in operand of OR or IN [message #29746 is a reply to message #29743]

Fri, 19 March 2004 06:45

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

This solution assumes that your times fields are datatype NUMBER.

You could use an in-line view in the following manner:

SELECT x....
,      x....
,      x....
FROM  (SELECT twt....
       ,      c....
       ,      b....
       ,      NVL(c.totalcostedtime
              ,   NVL(b.totalbillabletime
                  ,   NVL(u.totalunbilledtime
                      ,   NVL(a.totaltime
                          ,   t.dummy))))    times
       FROM   tworktype            twt
       ,      costed               c
       ,      billable             b
       ,      unbilled             u
       ,      alltime              at
       ,     (SELECT -1E99 dummy
              FROM   DUAL)         t
       WHERE twt.uniqueid = c.worktypeid (+)
       AND   twt.uniqueid = b.worktypeid (+)
       AND   twt.uniqueid = u.worktypeid (+)
       AND   twt.uniqueid = at.worktypeid (+) ) x
WHERE  x.times <> t.dummy

Note, if you had 9i, this would be an appropriate place to use COALESCE:

SELECT x....
,      x....
,      x....
FROM  (SELECT twt....
       ,      c....
       ,      b....
       ,      COALESCE(c.totalcostedtime
              ,        b.totalbillabletime
              ,        u.totalunbilledtime
              ,        a.totaltime
              ,        t.dummy)    times
       FROM   tworktype            twt
       ,      costed               c
       ,      billable             b
       ,      unbilled             u
       ,      alltime              at
       ,     (SELECT -1E99 dummy
              FROM   DUAL)         t
       WHERE twt.uniqueid = c.worktypeid (+)
       AND   twt.uniqueid = b.worktypeid (+)
       AND   twt.uniqueid = u.worktypeid (+)
       AND   twt.uniqueid = at.worktypeid (+) ) x
WHERE  x.times <> t.dummy

HTH,

Art.

Report message to a moderator

Previous Topic:	update statment
Next Topic:	add time to date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jun 30 05:49:52 CDT 2025