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
Garry Saddington <garry@schoolteachers.co.uk> 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
On Wed, Sep 24, 2008 at 3:22 PM, Garry Saddington
<garry@schoolteachers.co.uk> 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
Import Notes
Reply to msg id not found: 200809242322.16779.garry@schoolteachers.co.uk
On 24/09/2008 23:22, Garry Saddington wrote:
Garry Saddington <garry@schoolteachers.co.uk> writes:
CASE WHEN postcode ilike '%OO%' THEN ''
END
from addressesIt 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
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------
Import Notes
Resolved by subject fallback
On Wednesday 24 September 2008 21:03, Tom Lane wrote:
Garry Saddington <garry@schoolteachers.co.uk> 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
BTW, should you have an "else" clause in there? - What happens when the
comparison fails?
As Tom said, a null value would be returned.
