Re: Coalesce with Timestamp,Nulls,and Concatenation

On Thu, 2002-04-18 at 02:13, Pam Wampler wrote:
> can someone please give the correct syntax for a query that has a column
> timestamp that allows nulls and
> needs to be converted to a certain format and also needs to be concatenated
> with commas
>
> example:
>
> select to_char(timestamp1,'YYYYMMDD
> HH24:MI')||','||employee||','||to_char(timestamp2,'YYYYMMDD HH24:MI') from
> employee;
>
> How do I put the coalesce & also get the correct format and handle if the
> timestamps are nulls to ""?

Do you want the result of to_char coalesced to ""?

select coalesce( to_char(timestamp1,'YYYYMMDD HH24:MI'),'') || ',' ||
employee || ',' || coalesce( to_char(timestamp2,'YYYYMMDD HH24:MI'), '')
from employee;

Or do you want the null timestamp coalesced to some timestamp value?

select to_char( coalesce( timestamp1, 'now'::timestamp), 'YYYYMMDD
HH24:MI'),'') || ',' || employee || ',' || to_char( coalesce(
timestamp2, 'tomorrow'::timestamp), 'YYYYMMDD HH24:MI'), '') from
employee;

Regards,
                    Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?