Re: how to return field based on field= NULL or not

On 24 Aug 2009, at 7:50, Scott Marlowe wrote:

> On Sun, Aug 23, 2009 at 11:43 PM, Juan
> Backson<juanbackson(at)gmail(dot)com> wrote:
>> Hi,
>>
>> Thank you for your help.
>>
>> What I want to dos is as follows:
>>
>> SELECT COALESCE(fieldA::text,fieldB||fieldC||fieldD) from ring where
>> group_id = 1
>>
>> if fieldB is NULL, i will want it to return fieldC|| fieldD
>> if fieldB and fieldC is null, I want it to return fieldD.

I get the impression you mean || to mean C-style OR instead of SQL-
style concatenate? If not, Scott gave you the right solution already,
otherwise read on.

>> Basically, fieldD is always going to have data, but fieldB and
>> fieldC can be
>> NULL.
>>
>> How can I revise the query to meet that purpose?

SELECT CASE
WHEN fieldA IS NOT NULL THEN fieldA
WHEN fieldB IS NOT NULL THEN fieldB
WHEN fieldC IS NOT NULL THEN fieldC
ELSE fieldD
END
FROM ring WHERE group_id = 1;

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,4a92716d11861465718119!