Thread: 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 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
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
On 24/09/2008 23:22, Garry Saddington wrote: >> Garry Saddington <garry@schoolteachers.co.uk> 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 rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
BTW, should you have an "else" clause in there? - What happens when the
comparison fails?
As Tom said, a null value would be returned.
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