[GENERAL] case expression
Can anyone tell me why this will not work? select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses where studentid=1234 and addresstype='C' There are postcodes like this: OO00 0OO Regards Garry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
Garry Saddington [EMAIL PROTECTED] writes: Can anyone tell me why this will not work? select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses ... Define not work. What are you expecting it to do versus what really happens? Right offhand it looks like the CASE will return either an empty string or a NULL, which doesn't seem particularly useful ... regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
On Wednesday 24 September 2008 21:03, Tom Lane wrote: Garry Saddington [EMAIL PROTECTED] writes: Can anyone tell me why this will not work? select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses ... Define not work. What are you expecting it to do versus what really happens? Right offhand it looks like the CASE will return either an empty string or a NULL, which doesn't seem particularly useful ... regards, tom lane It does work but returns a column called case. How can I return the case column as 'postcode'? Regards garry -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
On Wed, Sep 24, 2008 at 3:22 PM, Garry Saddington [EMAIL PROTECTED] wrote: select *, CASE WHEN postcode ilike '%OO%' THEN '' END from addresses It does work but returns a column called case. How can I return the case column as 'postcode'? You have to give an Alias to this column name CASE WHEN postcode ilike '%OO%' THEN '' END AS postcode -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
On 24/09/2008 23:22, Garry Saddington wrote: Garry Saddington [EMAIL PROTECTED] writes: CASE WHEN postcode ilike '%OO%' THEN '' END from addresses It does work but returns a column called case. How can I return the case column as 'postcode'? ...case when postcode ilike '%OO%' then '' end as postcode, ... BTW, should you have an else clause in there? - What happens when the comparison fails? Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland [EMAIL PROTECTED] Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] case expression
BTW, should you have an else clause in there? - What happens when the comparison fails? As Tom said, a null value would be returned.
Re: [GENERAL] case expression
Garry Saddington wrote: It does work but returns a column called case. How can I return the case column as 'postcode'? select CASE WHEN postcode ilike '%OO%' THEN '' END as postcode from addresses -- Guy Rouillier -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
