DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Outer join query

  1. #1
    Join Date
    Jan 2002
    Posts
    1
    I am not very clear with the outer join concept .

    Table a has 1,2,3,4 values in quarter_num column .
    The attribute9 of the b table only has 4 defined as quarter
    defined for code combination id=4356

    currently , any where I put the (+) sign , I get
    result as because attribute9 only has 4

    quarter_num

    4

    but I want the result to be

    quarter_num
    1
    2
    3
    4

    ---------------------------------------------
    SELECT DISTINCT a.QUARTER_NUM
    FROM a,b
    WHERE a.PERIOD_YEAR='2001'
    AND SUBSTR(b.ATTRIBUTE9(+),5,1)=a.QUARTER_NUM
    AND b.CODE_COMBINATION_ID=4356

    or

    SELECT DISTINCT a.QUARTER_NUM
    FROM a,b
    WHERE a.PERIOD_YEAR='2001'
    AND SUBSTR(b.ATTRIBUTE9,5,1)=a.QUARTER_NUM (+)
    AND b.CODE_COMBINATION_ID=4356

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Code:
    SELECT DISTINCT a.QUARTER_NUM 
    FROM a,b 
    WHERE a.PERIOD_YEAR='2001' 
    AND SUBSTR(b.ATTRIBUTE9(+),5,1)=a.QUARTER_NUM 
    AND b.CODE_COMBINATION_ID(+)=4356
    Oracle documentation:
    If the WHERE clause contains a condition that compares a column from table B with a constant, the (+) operator must be applied to the column so that Oracle returns the rows from table A for which it has generated NULLs for this column. Otherwise Oracle will return only the results of a simple join.

Posting Permissions

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


Click Here to Expand Forum to Full Width