pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed

Using column alias in WHERE clause?
2+ messages / 2 participants
[nested] [flat]

* Using column alias in WHERE clause?
@ 2003-03-07 19:17  August Detlefsen <augustd@energiesonline.com>
  0 siblings, 1 reply; 2+ messages in thread

From: August Detlefsen @ 2003-03-07 19:17 UTC (permalink / raw)
  To: pgsql-sql

Is it possible to reference a column alias in the WHERE clause? 

I'd like to create a query something like this: 

SELECT cust_id,
CASE WHEN TRIM(UPPER(cust_name)) LIKE TRIM(UPPER( 'Joe Jones' )) THEN '2'   
     --SAME NAME
     WHEN TRIM(UPPER(alias)) LIKE TRIM(UPPER( '%Joe Jones%' ))   THEN '1'   
     --EXISTING ALIAS
     ELSE '0'  
     --NEW NAME
END AS name_test
FROM customer
WHERE cust_id = 1234
OR name_test > 0
ORDER BY name_test DESC

When I try it, I get: ERROR:  Attribute "name_test" not found

If I remove name_test from the WHERE clause, the query still runs fine with name_test in the ORDER BY. 

Is there a way to reference the alias in the Where clause as well? This is PostgreSQL 7.3.

Thanks,
August



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Using column alias in WHERE clause?
@ 2003-03-12 21:36  Rod Taylor <rbt@rbt.ca>
  parent: August Detlefsen <augustd@energiesonline.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Rod Taylor @ 2003-03-12 21:36 UTC (permalink / raw)
  To: August Detlefsen <augustd@energiesonline.com>; +Cc: pgsql-sql

On Fri, 2003-03-07 at 14:17, August Detlefsen wrote:
> Is it possible to reference a column alias in the WHERE clause? 
> 
> I'd like to create a query something like this: 
> 
> SELECT cust_id,
> CASE WHEN TRIM(UPPER(cust_name)) LIKE TRIM(UPPER( 'Joe Jones' )) THEN '2'   
>      --SAME NAME
>      WHEN TRIM(UPPER(alias)) LIKE TRIM(UPPER( '%Joe Jones%' ))   THEN '1'   
>      --EXISTING ALIAS
>      ELSE '0'  
>      --NEW NAME
> END AS name_test
> FROM customer
> WHERE cust_id = 1234
> OR name_test > 0
> ORDER BY name_test DESC


select cust_id
     , name_test
  FROM (select cust_id
             , CASE .... AS name_test
          from customer
         where cust_id = 1234
       ) as jnk
 where name_test > 0
 order by name_test desc;


> When I try it, I get: ERROR:  Attribute "name_test" not found
> 
> If I remove name_test from the WHERE clause, the query still runs fine with name_test in the ORDER BY. 
> 
> Is there a way to reference the alias in the Where clause as well? This is PostgreSQL 7.3.
> 
> Thanks,
> August
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc

Attachments:

  signature.asc (application/pgp-signature, 186B) download

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2003-03-12 21:36 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-07 19:17 Using column alias in WHERE clause? August Detlefsen <augustd@energiesonline.com>
2003-03-12 21:36 ` Rod Taylor <rbt@rbt.ca>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox