Column alias in where clause?
От | Jeff Ross |
---|---|
Тема | Column alias in where clause? |
Дата | |
Msg-id | 48A3102C.8090403@wykids.org обсуждение исходный текст |
Ответы |
Re: Column alias in where clause?
|
Список | 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 по дате отправления: