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

01-17-2002, 05:20 AM

ales

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.