Thread: Column alias in where clause?

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

On Wed, Aug 13, 2008 at 10:47:17AM -0600, Jeff Ross wrote:
> 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.

You can't. Conceptually, the result of the SELECT is not visible until
*after* the WHERE clause has executed, so having the where clause
depend on the select won't work.

What you can do is put the CASE stuff in a subquery:

SELECT * FROM
  (SELECT fc_facility_id, fc_name, CASE ...etc... )
WHERE fc_director_name ilike ('%Cobb%');

This way the inner select is done first, calculating the fields you
want, then the outer select can use the result.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

Martijn van Oosterhout wrote:
> On Wed, Aug 13, 2008 at 10:47:17AM -0600, Jeff Ross wrote:
>> 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.
>
> You can't. Conceptually, the result of the SELECT is not visible until
> *after* the WHERE clause has executed, so having the where clause
> depend on the select won't work.
>

Ah, now I get it!

> What you can do is put the CASE stuff in a subquery:
>
> SELECT * FROM
>   (SELECT fc_facility_id, fc_name, CASE ...etc... )
> WHERE fc_director_name ilike ('%Cobb%');
>
> This way the inner select is done first, calculating the fields you
> want, then the outer select can use the result.
>

Perfect!  Thank you!

Jeff

> Have a nice day,
>
>
> ------------------------------------------------------------------------
>
> !DSPAM:48a31748228425368819438!

Martijn van Oosterhout <kleptog@svana.org> writes:
> You can't. Conceptually, the result of the SELECT is not visible until
> *after* the WHERE clause has executed, so having the where clause
> depend on the select won't work.

It's not only conceptual.  Consider

    SELECT 1/x FROM mytab WHERE x <> 0;

You'd be unhappy if this query failed with a divide-by-zero error
(and it would be a violation of the SQL spec, too).  So per spec,
the SELECT list must not be evaluated until after WHERE completes,
and that's why it's not reasonable for WHERE to refer to the
SELECT list.

(Now, we've kinda broken this rule by allowing GROUP BY to refer
to SELECT items, but that's a matter for another discussion.)

            regards, tom lane