Search Postgresql Archives

Column alias in where clause?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux