CODEWITHSUNDEEP
sql
user3794853
user3794853

Reputation: 55

usage of case in SQL

I am trying to retrieve records from customer table with customername,city

custname|city
Anand|London
Paul|Rome
    .
    .
    .

however when retriving,if the city is London then Brussels should be displayed in its place and else original city name is to be displayed. I have tried thee following query'

select custname,case city when 'London' then 'Brussels' end from customer;

and

select custname,deocde(city,'London','Brussels') from customer;

Both are giving the result as:

custname|city
Anand|Brussels
Pau|

Other cities are not being displayed.How to write this query correctly.Please help me.Thanks in advance

Upvotes: 0

Views: 86

Answers (3)

Mr. Llama
Mr. Llama

Reputation: 20889

Decode has an "else" clause. The usual format is DECODE(column, search1, replace1[, search2, replace2][, default])

For example: DECODE(city, 'London', 'Brussels', city)

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions040.htm

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use the else clause:

select custname,
       (case city when 'London' then 'Brussels'
                  else city
        end) as city
from customer;

Upvotes: 2

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You should use ELSE:

select custname, case  
                    when city = 'London' then 'Brussels' 
                    else city 
                 end as city
from customer;

Upvotes: 2

Related Questions