Re: Problem with the outer join

From: Marc Billiet <someone.overthere_at_living>
Date: Wed, 06 Dec 2000 06:47:05 GMT
Message-ID: <20001206.6470551@slu40xxx.hae.hydro.com>

You can try this:

SELECT  emp.ID, emp.NAME,
        emp.DESCR,
        emp.MANID ,
        emp1.FIRSTNAME , emp1.LASTNAME ,
        emp.MANID,
        emp.DEFAULTID

FROM emp, emp1
WHERE (emp.UID=PID) and

       emp.MANID = emp.UID(+) -- I suppose you mean emp1.UID(+) UNION

SELECT  emp.ID, emp.NAME,
        emp.DESCR,
        emp.MANID ,
        emp1.FIRSTNAME , emp1.LASTNAME ,
        emp.MANID,
        emp.DEFAULTID

FROM emp, emp1
WHERE (emp.UID=PID) and

       emp.MANID = emp.UID(+) -- I suppose you mean emp1.UID(+) AND PSELECTID <> 0
AND (emp.UID IN

         (SELECT SEL.OBJECTID
         FROM SEL
         WHERE SEL.USERID = PSELECTID
         AND SEL.OBJECTTYPE = 29)))

ORDER BY 2; Marc

>>>>>>>>>>>>>>>>>> Oorspronkelijk bericht <<<<<<<<<<<<<<<<<<

Op 2000-12-06, 3:53:52, schreef Inna <mednyk_at_my-deja.com> over het thema  

Problem with the outer join:

> Hello, everyone.
> I have a big problem with the outer join.
> I migrated the SQL db to Oracle and right now I am working with
> procedures.I use the DBA studio. So when I compiled the
> procedures with outer join (+) and <OR> condition operator and <IN>
> using
> this tools I did not have any problem and was surprised because we can
 

> not use <OR> and <IN> with the (+) operator.
> But when I conected to the application, I received the error message
> from Oracle telling that these operators can not be used together.
> So there is my question: This is the fragment of the procedure body
> How can I make it valid and return the same result. If there is need o
 f
> functions or procedures I don't minde. Thank you an advance.
 

> PROCEDURE GETGROUPINFO
> (PID INTEGER,
> PSELECTID INTEGER,
> RC1 IN OUT RCT1)
> AS
> BEGIN
> OPEN RC1 FOR
> SELECT emp.ID, emp.NAME,
> emp.DESCR,
> emp.MANID ,
> emp1.FIRSTNAME , emp1.LASTNAME ,
> emp.MANID,
> emp.DEFAULTID
 

> FROM emp, emp1
 

> WHERE (emp.UID=PID) and
> emp.MANID = emp.UID(+)
> OR ((PSELECTID <> 0)
> AND (emp.UID IN
> (SELECT SEL.OBJECTID
> FROM SEL
> WHERE SEL.USERID = PSELECTID
> AND SEL.OBJECTTYPE = 29)))
 
> ORDER BY emp.NAME;
> END;
> END;

> --
> Inna.Junior programmer.

> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Dec 06 2000 - 00:47:05 CST