Results 1 to 5 of 5

Thread: Outer Join

  1. #1
    Join Date
    Mar 2013
    Posts
    30

    Unanswered: Outer Join

    I am trying to do a Left outer join I wanted all GL Accounts from GLMST to show up I queried GL account# 55000 (GLMST.GMSG02) to show up, if I input prior year 2013, 2012, 2014 (GLDET.GTPOYR) I will see the Account Description (GLMST.GMDSC1) and I will see Amount (GLDET.GTTRAM), but when I query for 2014 I get no row (there was no amount for 2014) I want to show the Account Description (GMDSC1) even if there is no amount. Below is my SQL query based on DB2. I anybody any point out what is wrong I will appreciate your help. Thank you in advance.


    SELECT GLMST.GMSG02 AS "GL Account No", GLMST.GMDSC1 AS "Account Description",
    GLDET.GTPOYR AS "Year", GLDET.GTTRAM AS Amount
    FROM { oj GLMST LEFT OUTER JOIN
    GLDET ON GLMST.GMIACN = GLDET.GTIACN }
    WHERE (GLMST.GMSG02 = 55000) AND (GLDET.GTPOYR = 2014)


    GLMST.GMIACN and GLDET.GTIACN are internal account numbers.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This seems one of typical misunderstandings related to outer join.

    Please consider the process sequence of clauses.
    Quote Originally Posted by tonkuma View Post
    ...

    ...
    Please see
    The clauses of the subselect are processed in the following sequence:
    1. FROM clause
    2. WHERE clause
    3. GROUP BY clause
    4. HAVING clause
    5. SELECT clause
    6. ORDER BY clause
    7. FETCH FIRST clause
    in
    http://www-01.ibm.com/support/knowle....1.0%2F2-9-6-1
    DB2 for Linux UNIX and Windows 10.1.0 > Database fundamentals > SQL > Queries > subselect

    ...
    ...
    WHERE conditions are applied afer FROM clause was processed.

    ... (there was no amount for 2014)
    Then, all GLDET.GTPOYR are NULL, after LEFT OUTER JOIN was processed in FROM clause.
    "GLDET.GTPOYR = 2014"(in WHERE clause) is unknown. So, that row wouldn't be selected.

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Whenever you have a left outer join all predicates for the table being outer joined to should be in the ON clause. If you put a predicate for this table into the WHERE clause, you have now turned it back into an INNER join. Not only will results not be correct, but performance will be adversely affected as well.
    Dave

  4. #4
    Join Date
    Mar 2013
    Posts
    30
    Thank you.

  5. #5
    Join Date
    Mar 2013
    Posts
    30
    Quote Originally Posted by tonkuma View Post
    This seems one of typical misunderstandings related to outer join.

    Please consider the process sequence of clauses.

    WHERE conditions are applied afer FROM clause was processed.


    Then, all GLDET.GTPOYR are NULL, after LEFT OUTER JOIN was processed in FROM clause.
    "GLDET.GTPOYR = 2014"(in WHERE clause) is unknown. So, that row wouldn't be selected.



    Thank you.

Posting Permissions

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