SELECT a.*
FROM Table_One a,
     Table_Two b
WHERE a.Fieldc IN (SELECT CompareValue from Table_Three cv WHERE inDate between cv.Date_ and SYSDATE)
AND a.Fielda = b.Fieldb(+)

Fielda | Fieldb | Fieldc|
'aVal' | 'bVal' | 'cVal'|
'dVal' | 'eVal  | 'fVal'|
'dVal' | 'fVal  | 'eVal'|

Fielda | Fieldb | Fieldc|
'aVal' | 'bVal' | 'cVal'|
'dVal' | 'fVal  | 'gVal'|
'dVal' | 'fVal  | 'cVal'|

CompareValue | date_      |
'fval'       | 2012-09-10 |

SELECT NVL(CompareValue,'dummy')
  from Table_Three cv WHERE inDate between cv.Date_ and SYSDATE