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