Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2008
    Posts
    464

    Unanswered: WHERE TABLE1=TABLE2(+) - 10g

    Hi guys

    Probably very basic for you guys but I couldn't find anything useful on the Internet.

    I have a query which selects data from 2 tables with aliases A and B.

    When I use the condition:

    Code:
    SELECT A.DATETIME,A.COL1,A.COL2, (A.COL3+B.COL4) ABC
    FROM TABLE1 A, TABLE2 B
    WHERE A.DATETIME BETWEEN TRUNC(SYSDATE)-1 and TRUNC(SYSDATE)
    AND A.DATETIME = B.DATETIME (+) AND A.COL1 = B. COL1(+) AND A.COL3 = B.COL3 (+)
    GROUP BY A.DATETIME,A.COL1,A.COL2
    I get all zeros.

    but when I use:

    Code:
    SELECT B.DATETIME,B.COL1,B.COL2, (A.COL3+B.COL4) ABC
    FROM TABLE1 A, TABLE2 B
    WHERE A.DATETIME BETWEEN TRUNC(SYSDATE)-1 and TRUNC(SYSDATE)
    AND A.DATETIME(+) = B.DATETIME  AND A.COL1(+) = B. COL1 AND A.COL3(+) = B.COL3 
    GROUP BY B.DATETIME,B.COL1,B.COL2
    I get the right values.

    I'd appreciate if someone could help me understand how data is being selected in both cases?

    Note: The first query is supposed to return the correct results instead of zeros.

    Regards
    Shajju
    Last edited by shajju; 01-30-14 at 03:30.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    The first query is supposed to return the correct results
    How do you know? I mean, if it turns out that the second query returns correct result, then, hm? Anyway: use Google and read about outer joins.

  3. #3
    Join Date
    Aug 2008
    Posts
    464
    Thanks.

    Sorry the comparison should be:

    Code:
    AND A.DATETIME = B.DATETIME (+) AND A.COL1 = B. COL1(+) AND A.COL2 = B.COL2 (+)
    instead of

    Code:
    AND A.DATETIME = B.DATETIME (+) AND A.COL1 = B. COL1(+) AND A.COL3 = B.COL3 (+)
    The first query returns proper results for all days of this month apart from 2. For those 2 days, I get zeros.

    I will read up on Outer Joins but could you explain in ur own words too please?

  4. #4
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Please stop using the old deprecated Oracle specific outer join syntax. Learn to use explicit `LEFT JOIN`s in the from clause
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •