Column alias in where clause?

Поиск
Список
Период
Сортировка
От Jeff Ross
Тема Column alias in where clause?
Дата
Msg-id 48A3102C.8090403@wykids.org
обсуждение исходный текст
Ответы Re: Column alias in where clause?  (Martijn van Oosterhout <kleptog@svana.org>)
Список pgsql-general
I'm a little confused about how to use a column alias in the where
clause of a query.  I'm sure the answer is something simple, but I
haven't found anything searching through Google or from reading the docs.

I inherited a table that used to store the name of a facility director
as the actual name.  I'm transitioning to using a pointer to a people
table, but until the transition is complete, I want to be able to show
the old information if the id is null, or the new information if the id
is not null.

I wrote this query to search the facilities by the director's name:

SELECT fc_facility_id, fc_name,
     CASE
       WHEN fc_director_id is null
    THEN fc_director_last_name || ', ' || fc_director_first_name
       WHEN fc_director_id is not null
    THEN pp_last_name || ', ' || pp_first_name
     END as "fc_director_name",
     CASE
       WHEN fc_director_id is null
    THEN fc_director_last_name
       WHEN fc_director_id is not null
     THEN pp_last_name
     END as "fc_director_last_name",
     CASE
       WHEN fc_director_id is null
    THEN fc_director_first_name
       WHEN fc_director_id is not null
    THEN pp_first_name
     END as "fc_director_first_name",
     fc_mailing_city, fc_type, fc_license_end_date, fc_license_status
     FROM facilities
     LEFT JOIN people ON fc_director_id = pp_id
     WHERE fc_director_name ilike ('%Cobb%');

but I get this error

ERROR:  column "fc_director_name" does not exist
LINE 23:     WHERE fc_director_name ilike ('%Cobb%');

I've also written the where clause using double quotes around the column
name but I get the same error.

The documentation for SELECT says that "When an alias is provided, it
completely hides the actual name of the table or function; for example
given FROM foo AS f, the remainder of the SELECT must refer to this FROM
item as f not foo.".

Does this apply in the WHERE clause as well?  If it doesn't how can I
refer to the results of the case statements later in the where clause?

Thanks,

Jeff Ross

В списке pgsql-general по дате отправления:

Предыдущее
От: Martijn van Oosterhout
Дата:
Сообщение: Re: automatic REINDEX-ing
Следующее
От: Joao Ferreira gmail
Дата:
Сообщение: Re: automatic REINDEX-ing