Display any value instead of null?

Question

• Database: Tennis

• Table: Players

• Some of the Columns: playerno, first name, last name, leagueno.

• Task: If the league number is NULL, then give the value 1.

• Question: Can we do this without coalesce function ? or without any other function ?

• My code is wrong. I still see a null instead of 1. Besides, there is the unnecessary column due to the case.

Code:

use tennis;
select playerno, name, initials,leagueno,
case 
when leagueno = null then 1 
end
from players
where tennis.players.town = 'Stratford'
order by leagueno desc;

Please help me to do it correctly. I have the answer which uses coalesce. But i want to try another method.

Answer 1

I think what you want is this:

use tennis;

select playerno, name, initials,
case 
when leagueno is null then 1 -- note: is null instead of = null
else leagueno
end as leagueno -- This names the result column "leagueno", which may be useful
                -- depending on how you read the result
from players
where tennis.players.town = 'Stratford'
order by leagueno desc;

This basically makes the last column leagueno except that if it's NULL, you get 1 instead.