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

2009-08-24 Thread Alban Hertroys 

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

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-08-24 Thread Sam Mason 
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/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] how to return field based on field= NULL or not

2009-08-23 Thread Juan Backson 
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

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

2009-08-23 Thread Adam Rich 




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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-08-23 Thread Wojtek 

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



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2009-08-23 Thread Scott Marlowe 
On Sun, Aug 23, 2009 at 11:43 PM, Juan Backsonjuanback...@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 ...

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general