Use coalesce() as in where clause

[Bob Bedford]

I've a query like this:

select coealesce(table 1.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

[Matt Mitchell]

Re: Use coalesce() as in where clause


"Bob Bedford" <bedford1@notfo rspammershotmai l.com> wrote in message
news:42199b3f$0 $3412$5402220f@ news.sunrise.ch ...
: I've a query like this:
:
: select coealesce(table 1.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='X XX' or table2.code1 = 'XXX';

I think the second might be faster...

Matt