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 -----------------------------------------------------------------