SQL Case with multiple fields

Question

I have 3 fields that shows a hierachy in my application. The 3 fileds are as follows:

rl.Level1
rl.Level2
rl.Level3

I am trying to show only the last level that is populated in my output.

Basically this is what I am trying to do. If level3 is null then return level2 but if level2 is also null then return level1 but if level3 is not null then return level3.

Level1 will always be not null.

This is in my select clause so would I do this with a case statment?

Thanks!

Answer 1

Try

COALESCE (rl.Level3,rl.Level2, rl.Level1)

Answer 2

use coalesce

select coalesce(rl.Level3,rl.Level2,rl.Level1) as SomeCol
from SomeTable

It will return the first non null value

Answer 3

use COALESCE function http://msdn.microsoft.com/en-us/library/aa258244(SQL.80).aspx

Answer 4

SELECT COALESCE(rl.Level3, rl.Level2, rl.Level1)