Use coalesce() as in where clause

I've a query like this:

select coealesce(table1.code1, table2.code1) as mycode
from...
where mycode = 'XXX'

But this query fails. Unknow column mycode in where clause.

why ??? "coalesce() as" doesn't work ?

Bob

"Bob Bedford" <bedford1@notforspammershotmail.com> wrote in message
news:42199b3f$0$3412$5402220f@news.sunrise.ch...
: I've a query like this:
:
: select coealesce(table1.code1, table2.code1) as mycode
: from...
: where mycode = 'XXX'
:
: But this query fails. Unknow column mycode in where clause.
:
: why ??? "coalesce() as" doesn't work ?

select coalesce(table1.code1, table2.code1) as mycode
from ...
where coalesce(table1.code1, table2.code1) = 'XXX';


OR

select coalesce(table1.code1, table2.code1) as mycode
from ...
where table1.code1='XXX' or table2.code1 = 'XXX';

I think the second might be faster...

Matt