View with outer join

  • From: jaysingh1@xxxxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 04 Feb 2004 15:25:35 -0500

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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: