View with outer join

From: <jaysingh1_at_optonline.net>
Date: Wed, 04 Feb 2004 15:25:35 -0500
Message-id: <ee823fee54df.ee54dfee823f@optonline.net>

Dear All,

I have problem with view which has outer join. Could someone in this list please through some light how to accomplish Step(1) output using Step(2) and Step(3)?

Isn't it "Step(1) = Step(2)+Step(3)"?

Step(1) returns 24 records.
Step(2)+Step(3) returns 0 records.

Please enlighten me if I am wrong?

STEP (1)

SELECT T1.USERSTATUSID,

      NVL(T21.DESCRIPTION,T22.DESCRIPTION) USERSTATUSDESC,
      NVL(T21.TOKENVALUE,T22.TOKENVALUE) USERSTATUSABBREV,
         T1.HSBCSERVICEID,
         T1.ACTIVEFLAG,
         T21.LANGUAGEID LANGUAGEID1,
     T22.LANGUAGEID LANGUAGEID2,
         T1.UPDATEDATE,
         T1.CREATEDATE
FROM    USERSTATUS T1,
        crmf.NLS_TOKEN_DETAILS T21,
        crmf.NLS_TOKEN_DETAILS T22
WHERE   T1.TOKENID=T21.TOKENID(+)
AND     T1.TOKENID=T22.TOKENID
AND     T22.LANGUAGEID=30
AND     T21.languageid(+)=1;

24 rows returned. <=========********

STEP (2)

CREATE OR REPLACE VIEW userstatus_v2 AS
SELECT T1.USERSTATUSID,

       NVL(T21.DESCRIPTION,T22.DESCRIPTION) USERSTATUSDESC,
       NVL(T21.TOKENVALUE,T22.TOKENVALUE) USERSTATUSABBREV,
                 T1.HSBCSERVICEID,
                 T1.ACTIVEFLAG,
                 T21.LANGUAGEID LANGUAGEID1,
         T22.LANGUAGEID LANGUAGEID2,
                 T1.UPDATEDATE,
                 T1.CREATEDATE
FROM     USERSTATUS T1,
         crmf.NLS_TOKEN_DETAILS T21,
         crmf.NLS_TOKEN_DETAILS T22
WHERE    T1.TOKENID=T21.TOKENID(+)
AND      T1.TOKENID=T22.TOKENID
AND      T22.LANGUAGEID=30;

View created.

STEP (3)

SELECT * FROM userstatus_v2
WHERE languageid1(+)=1;
no rows returned. <==========*******

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Feb 04 2004 - 14:25:35 CST