MVSFORUMS.com

vkphani · Intermediate Joined: 05 Sep 2003 Posts: 483 Topics: 48

Hi,
I am facing some problem with the function Coalesce.
I used this function as below:
COALESCE(EUC_UPD_USR_ID,' '),
COALESCE(EUC_GE_REAS_CD,0)
Here EUC_UPD_USR_ID is an alphanumeric field and EUC_GE_REAS_CD is a numeric field.
This is working fine.

I used the same function for a Timestamp field as below:
COALESCE(EUC_UPD_TMST,' ')
Here EUC_UPD_TMST is an alphanumeric field.

But if I use this for a Timestamp field it is saying that THE DATA TYPE, LENGTH OF COALESCE IS INVALID.

Can anybody pls help me on this.

kolusu · Posted: Mon Aug 16, 2004 5:40 am Post subject:

paneendra,

Try

vkphani · Intermediate Joined: 05 Sep 2003 Posts: 483 Topics: 48

Kolusu,
Even moving '0000-00-00-00.00.00.000000' is giving the sql code -171(THE DATA TYPE, LENGTH OF COALESCE IS INVALID).

kolusu · Posted: Mon Aug 16, 2004 7:47 am Post subject:

Paneendra,

Try

vkphani · Intermediate Joined: 05 Sep 2003 Posts: 483 Topics: 48

Thanks Kolusu,
This syntax is working.

pzmohanty · Posted: Sun Sep 05, 2004 5:50 am Post subject:

hi,

another way can be :

COALESCE(EUC_UPD_TMST, TIMESTAMP('0000-00-00-00.00.00.000000 '))
or
VALUE(EUC_UPD_TMST, TIMESTAMP('0000-00-00-00.00.00.000000 '))

Thanx
P.R.Mohanty
_________________
Priya Ranjan Mohanty
Consultant
Kanbay Software (I) pvt. Ltd.
Hyderabad