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

Hi,

In my table, I have fieldA and fieldB.  At any point in time, only one of these fields can have data in it.  The other is NULL.

Instead of "select fieldA, fieldB from table", I want it to return either fieldA or fieldB depends on whether it is NULL or not.

The reason is because I want to use "select array_to_string(array_accum(field A or field B) ,',') from table.

Is it possible to do it that way?

Thanks,
JB

Juan Backson wrote:
> Hi,
>
> In my table, I have fieldA and fieldB.  At any point in time, only one
> of these fields can have data in it.  The other is NULL.
>
> Instead of "select fieldA, fieldB from table", I want it to return
> either fieldA or fieldB depends on whether it is NULL or not.
>
> The reason is because I want to use "select
> array_to_string(array_accum(field A or field B) ,',') from table.
>
> Is it possible to do it that way?
>
> Thanks,
> JB


The two main ways of doing this are COALESCE(fieldA, fieldB)

http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html#AEN14484

and CASE WHEN fieldA IS NULL THEN fieldB ELSE fieldA END;

http://www.postgresql.org/docs/8.3/interactive/functions-conditional.html#AEN14434

Hi,

I assume  one field will always be NULL and one will always has data in it.

select coalesce(fieldA, fieldB) from table

Regards,
foo

Juan Backson wrote:
> Hi,
>
> In my table, I have fieldA and fieldB.  At any point in time, only one
> of these fields can have data in it.  The other is NULL.
>
> Instead of "select fieldA, fieldB from table", I want it to return
> either fieldA or fieldB depends on whether it is NULL or not.
>
> The reason is because I want to use "select
> array_to_string(array_accum(field A or field B) ,',') from table.
>
> Is it possible to do it that way?
>
> Thanks,
> JB

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.

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?

Thanks,
JB

On Sun, Aug 23, 2009 at 11:43 PM, Juan Backson<juanbackson@gmail.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.
>
> 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?

Just coalesce them all to '' and cat them:
... coalesce(a,'')||coalesce(b,'')||coalesce(c,'')||d ...

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

> On Sun, Aug 23, 2009 at 11:43 PM, Juan
> Backson<juanbackson@gmail.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!

On Mon, Aug 24, 2009 at 12:54:31PM +0200, Alban Hertroys wrote:
>     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

BTW, the above expression is identical to:

  COALESCE(fieldA,fieldB,fieldC,fieldD)

--
  Sam  http://samason.me.uk/