Thread: Coalesce with Timestamp,Nulls,and Concatenation

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 ""?

thanks

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?

Hi all,

In my table, a TEXT column is a unique column, by default it allows "denis"
and "DENIS" to be entered in same column.

I want the database to ignore CASE for considering uniqueness of the data.

for e.g. once "Denis" is entered, it should not allow to enter "denis",
"Denis", "DENIS" , "DeNiS" ...

Thanks

Denis