how to return field based on field= NULL or not

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,

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 Mon, Aug 24, 2009 at 11:55 AM, Wojtek <foo@twine.pl> 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.

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/