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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
