Re: View with outer join

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Thu, 5 Feb 2004 07:50:45 -0500
Message-ID: <25144750920.20040205075045@gennick.com>

Your view returns rows, right? Test that first, but I believe if you issue a "SELECT * FROM userstatus_v2", you should get plenty of rows back.

I think the problem with your query in step 3 is that you are trying to include the "(+)" operator in a view-using query, and you want that operator to act as if it were part of the view-defining query:

SELECT * FROM userstatus_v2
WHERE languageid1(+)=1;

Conceptually, by the time you execute this query, the outer-join has already taken place. Therefore, the languageid1 column should be either 1 or NULL. You might try rewriting your step 3 query as follows:

SELECT * FROM userstatus_v2
WHERE languageid1=1

   OR languageid1 IS NULL;

Try this. See whether it helps. I wish I had some data to test it on myself, but I don't.

Oh, by the way, if you're running on 9i I recommend using the newer, outer-join syntax in your view-defining query.

Best regards,

Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.

Wednesday, February 4, 2004, 3:25:35 PM, jaysingh1_at_optonline.net (jaysingh1_at_optonline.net) wrote: jon> Dear All,

jon> 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)?

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

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

jon> Please enlighten me if I am wrong?

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

jon> 24 rows returned. <=========********

jon> STEP (2)
jon> ========
jon> CREATE OR REPLACE VIEW userstatus_v2 AS
jon> SELECT   T1.USERSTATUSID,
jon>        NVL(T21.DESCRIPTION,T22.DESCRIPTION) USERSTATUSDESC,
jon>        NVL(T21.TOKENVALUE,T22.TOKENVALUE) USERSTATUSABBREV,
jon>                  T1.HSBCSERVICEID,
jon>                  T1.ACTIVEFLAG,
jon>                  T21.LANGUAGEID LANGUAGEID1,
jon>          T22.LANGUAGEID LANGUAGEID2,
jon>                  T1.UPDATEDATE,
jon>                  T1.CREATEDATE
jon> FROM     USERSTATUS T1,
jon>          crmf.NLS_TOKEN_DETAILS T21,
jon>          crmf.NLS_TOKEN_DETAILS T22
jon> WHERE    T1.TOKENID=T21.TOKENID(+)
jon> AND      T1.TOKENID=T22.TOKENID
jon> AND      T22.LANGUAGEID=30;

jon> View created.

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


jon> ----------------------------------------------------------------
jon> Please see the official ORACLE-L FAQ: http://www.orafaq.com
jon> ----------------------------------------------------------------
jon> To unsubscribe send email to:  oracle-l-request_at_freelists.org
jon> put 'unsubscribe' in the subject line.
jon> --
jon> Archives are at http://www.freelists.org/archives/oracle-l/

jon> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

jon> -----------------------------------------------------------------



----------------------------------------------------------------

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 Thu Feb 05 2004 - 06:50:45 CST