[RESOLVED] Oracle 9i: Alias in WHERE clause

[nmadd]

So, it appears that I can't use an Alias in a WHERE clause with Oracle 9i? ORA-00904:"SELLOFFDATE":Invalid Identifier is the error.

If that is the case, how shall I perform the comparison in my WHERE clause below? If I'm simply making some SQL error, let me know that too.

Here's a snippet of what I'd like to do. Thanks for any help!

select 
  (select to_date(r2.FIELD_VALUE, 'dd-mon-yyyy')
  from V_REPCUSTOMFIELDDATA r2
  where
    r2.INTERNAL_DESCRIPTIONS_CODE = 35
    and r2.ASSOCIATED_WITH_CODE = ag.AGREEMENTS_ABBR) SellOffDate,
  ag.AGREEMENTS_ABBR LicensorNumber
from V_AGREEMENT ag
where SellOffDate between 
    to_date('01/01/1999', 'mm/dd/yyyy') and to_date('12/31/2001', 'mm/dd/yyyy')

[Jrogers]

you can put your where condition in your subquery

SELECT 
  (SELECT to_date(r2.FIELD_VALUE, 'dd-mon-yyyy')
  FROM V_REPCUSTOMFIELDDATA r2
  WHERE
    r2.INTERNAL_DESCRIPTIONS_CODE = 35
    AND r2.ASSOCIATED_WITH_CODE = ag.AGREEMENTS_ABBR
    AND to_date(r2.FIELD_VALUE, 'dd-mon-yyyy')  BETWEEN 
    to_date('01/01/1999', 'mm/dd/yyyy') AND to_date('12/31/2001', 'mm/dd/yyyy') ) SellOffDate,
  ag.AGREEMENTS_ABBR LicensorNumber
FROM V_AGREEMENT ag

[nmadd]

Easy enough. That seems to work! Thanks!