Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Unanswered: Outer Join(+) Operator

    Hi
    I am using oracle Version 8.1.6/8.1.7

    Example: - OPEN RC4 FOR SELECT
    BPJ.ID,
    BPJ.CID,
    BPJ.PID,
    BPJ.INC,
    VBC.BID,
    VBC.CID,
    TCL.NAME,
    TCL.ID,
    TPR.NAME,
    TPR.ID,
    TPR.STARTDATE,
    TPR.ENDDATE,
    TINV.BNO,
    TINV.NAME
    FROM BPJ,
    VBC,
    TCL,
    TINV,
    TPR,
    TPJST
    WHERE BPJ.CID = VBC.ID
    AND VBC.CID = TCL.ID
    AND VBC.BID = TINV.ID
    AND BPJ.PID = TPR.ID
    AND TPJST.CID (+) = VBC.CID
    AND TPJST.PID (+) = BPJ.PID
    OR BPJ.ID = PID;

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

    I am getting the following error message when executing a select query with outer join, any ideas? How could i modify this query inorder to overcome this error?
    Last edited by Anderson; 06-09-04 at 02:35. Reason: InComplete

  2. #2
    Join Date
    Jun 2004
    Posts
    7
    I usually use brackets to signify exactly what is being 'OR'ed.
    One way to get around the problem may be to use a union
    to replace the OR.

    Select
    first side of the or
    union
    Select
    second side of the or

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    There are ways around this, but it's difficult to say in this case because I don't understand what your SQL is doing.

    1) What is "PID" on the right hand side of this:

    OR BPJ.ID = PID

    Do you mean BPJ.PID or TPJST.PID or something else? Oracle won't know either, so this will cause another error:

    ORA-00918: column ambiguously defined

    2) How should that OR be read? As you have it, with no brackets, it will be treated as:
    Code:
    WHERE (BPJ.CID = VBC.ID
          AND VBC.CID = TCL.ID
          AND VBC.BID = TINV.ID
          AND BPJ.PID = TPR.ID
          AND TPJST.CID (+) = VBC.CID
          AND TPJST.PID (+) = BPJ.PID)
    OR (BPJ.ID = PID);
    That turns the whole query into a cartesian product. Perhaps you really meant:
    Code:
    WHERE BPJ.CID = VBC.ID
    AND VBC.CID = TCL.ID
    AND VBC.BID = TINV.ID
    AND BPJ.PID = TPR.ID
    AND TPJST.CID (+) = VBC.CID
    AND (TPJST.PID (+) = BPJ.PID OR BPJ.ID = PID);

  4. #4
    Join Date
    Jan 2004
    Posts
    30
    This is my original which i had used it in one of my stored procedure.

    Example: - OPEN RC4 FOR SELECT
    BPJ.ID,
    BPJ.CID,
    BPJ.PID,
    BPJ.INC,
    VBC.BID,
    VBC.CID,
    TCL.NAME,
    TCL.ID,
    TPR.NAME,
    TPR.ID,
    TPR.STARTDATE,
    TPR.ENDDATE,
    TINV.BNO,
    TINV.NAME
    FROM BPJ,
    VBC,
    TCL,
    TINV,
    TPR,
    TPJST
    WHERE BPJ.CID = VBC.ID
    AND VBC.CID = TCL.ID
    AND VBC.BID = TINV.ID
    AND BPJ.PID = TPR.ID
    AND TPJST.CID (+) = VBC.CID
    AND ((TPJST.PID (+) = BPJ.PID)
    OR (BPJ.ID = LocalPID));
    Note :-LocalPID is the local variable which i had used in my stored procedure.
    Ofcourse i can use union operator to overcome this issue but by union operator it takes long time to retrive the result. is there any other way i can solve this issue?
    Last edited by Anderson; 06-09-04 at 07:06.

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You want to retrieve the information for the given ID, right?
    Then you should change the OR to AND

    Regards

  6. #6
    Join Date
    Jan 2004
    Posts
    30
    Hi namliam,

    Thanks for your reply. Is it that i have to use AND instead of OR in my query? by changing it whether query results remain unchanged? Can u please explain me with the example which is in my previous post.

  7. #7
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Why didn't you post your real code the first time, instead of a different and invalid query?

    Anyway, I still can't get my head around what this means:

    ((TPJST.PID (+) = BPJ.PID) OR (BPJ.ID = LocalPID))

    It seems to be saying something like:

    1) Select all BPJ records whether the ID matched LocalPID or not.
    2) If the ID does not match LocalPID then outer join BPJ to TPJST, otherwise don't


    I somehow don't think that is what you meant?

  8. #8
    Join Date
    Jan 2004
    Posts
    30
    sorry, i might confused u a lot what u had said was right for that query. Let forget about that query. Hope the following simple query(the same error occurs with this query) will help u to get me a solution

    OPEN RC4 FOR SELECT
    BPJ.ID,
    BPJ.CID,
    BPJ.PID,
    BPJ.INC,
    VBC.CID,
    TCL.ID,
    TPR.ID,
    TINV.BNO,
    FROM BPJ, VBC, TCL, TINV, TPR
    WHERE BPJ.CID = VBC.ID
    AND TCL.ID = TINV.BNO
    AND TINV.BNO (+)= TPR.ID
    AND BPJ.ID (+) = VBC.CID
    AND ((PINCLUDE = - 1)
    OR (BPJ.PID = PINCLUDE))
    ;

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Well, I just created the tables for that query like this:

    create table BPJ(ID int, CID int, PID int, INC int);
    create table VBC(ID int, CID int);
    create table TCL(ID int);
    create table TPR(ID int);
    create table TINV(BNO int);

    and when I first ran your SELECT against it I got this:

    FROM BPJ, VBC, TCL, TINV, TPR
    *
    ERROR at line 10:
    ORA-00936: missing expression

    Then I fixed the syntax error (removed the comma after TINV.BNO) and it ran without error:

    1 declare
    2 rc4 utils.ref_cursor;
    3 pinclude integer;
    4 begin
    5 OPEN RC4 FOR SELECT
    6 BPJ.ID,
    7 BPJ.CID,
    8 BPJ.PID,
    9 BPJ.INC,
    10 VBC.CID,
    11 TCL.ID,
    12 TPR.ID,
    13 TINV.BNO
    14 FROM BPJ, VBC, TCL, TINV, TPR
    15 WHERE BPJ.CID = VBC.ID
    16 AND TCL.ID = TINV.BNO
    17 AND TINV.BNO (+)= TPR.ID
    18 AND BPJ.ID (+) = VBC.CID
    19 AND ((PINCLUDE = - 1)
    20 OR (BPJ.PID = PINCLUDE));
    21* END;
    SQL> /

    PL/SQL procedure successfully completed.

  10. #10
    Join Date
    Jan 2004
    Posts
    30
    Thanks for your prompt reply. Ofcourse there won't be any error at the time of compilation, the error occurs when i try to execute the stored procedure which contains this query from application.

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

    Any idea i could oversome this error.

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    But I did not get that error when I executed it in my previous example! The error message seems to suggest that there is an outer join within the OR part, e.g.

    AND ((PINCLUDE = - 1)
    OR (BPJ.PID (+) = PINCLUDE))

  12. #12
    Join Date
    Jan 2004
    Posts
    30
    Are u trying with the oracle 8.1.7?. I am getting this error with this version when executing through the application.

  13. #13
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, I am on 8.1.7 too:

    SQL> select * from v$version;

    BANNER
    -----------------------------------------------------------
    Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
    PL/SQL Release 8.1.7.3.0 - Production
    CORE 8.1.7.0.0 Production
    TNS for Solaris: Version 8.1.7.3.0 - Production
    NLSRTL Version 3.4.1.0.0 - Production

    SQL> declare
    2 rc4 utils.ref_cursor;
    3 pinclude integer;
    4 begin
    5 OPEN RC4 FOR SELECT
    6 BPJ.ID,
    7 BPJ.CID,
    8 BPJ.PID,
    9 BPJ.INC,
    10 VBC.CID,
    11 TCL.ID,
    12 TPR.ID,
    13 TINV.BNO
    14 FROM BPJ, VBC, TCL, TINV, TPR
    15 WHERE BPJ.CID = VBC.ID
    16 AND TCL.ID = TINV.BNO
    17 AND TINV.BNO (+)= TPR.ID
    18 AND BPJ.ID (+) = VBC.CID
    19 AND ((PINCLUDE = - 1)
    20 OR (BPJ.PID = PINCLUDE));
    21 END;
    22 /

    PL/SQL procedure successfully completed.

    Can you run this same PL/SQL block? :-
    Code:
    declare
      rc4 utils.ref_cursor;
      pinclude integer;
    begin
    OPEN RC4 FOR SELECT
    BPJ.ID,
    BPJ.CID,
    BPJ.PID,
    BPJ.INC,
    VBC.CID,
    TCL.ID,
    TPR.ID,
    TINV.BNO
    FROM BPJ, VBC, TCL, TINV, TPR
    WHERE BPJ.CID = VBC.ID
    AND TCL.ID = TINV.BNO
    AND TINV.BNO (+)= TPR.ID
    AND BPJ.ID (+) = VBC.CID
    AND ((PINCLUDE = - 1)
    OR (BPJ.PID = PINCLUDE));
    END;

  14. #14
    Join Date
    Jan 2004
    Posts
    30
    Sorry for my late reply. yes i could excute the block in sql. but the problem is when i try to access the same SQL block through Application(VB/.NET) from outside i am getting the mentioned error. Any idea that i could rewrite the query with out OR condition in it so that i could overcome this error?.

  15. #15
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It seems to me that your VB app cannot be running the same SQL statement! SQL either works or does not work, Oracle doesn't care whether you submitted the SQL statement from SQL Plus, VB or any other client application.

    However, if desparate you could try:

    ...
    AND NVL(BPJ.PID,-999) = DECODE(PINCLUDE,-1,NVL(BPJ.PID,-999),PINCLUDE);

    (Here -999 should be any value that you never expect to find in BPJ.PID)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •