case and isnull for multiple fields

[subtrakt]

HI,

Working on a query that if one field is null then it uses another field and if that field isnull it uses another. Will case work like that in a linear operation left-to-right or is there a better option?

eval main=case(isnull(test1),test2,test1,isnull(test2),test3,test2,isnull(test3),test4,test3,1=1,"All Test Are Null)

[davebrooking]

Take a look at the eval coalesce function - as the documentation states

This function takes an arbitrary number of arguments and returns the first value that is not null.

[vasanthmss · Accepted solution]

Try this,

| eval main = case(isnotnull(test1), test1, isnotnull(test2), test2, isnotnull(test3), test3, isnotnull(test4), test4, 1=1 , "All Test Are Null")

Main's value should be test1 / test2 / test3 / test4 in-case test1 is empty option goes to test2, if test2 is empty then option goes to test 3 and test4 like wise.

If suppose test1, test2, test3, test4 contains value then test1 would be assigned to main. if not "All Test are Null" will be assigned to main.

Hope this will help you.

[subtrakt]

Thanks - worked like a dream

[vasanthmss]

Cheers !!!