DSXchange

Posted: **Thu Aug 14, 2008 4:49 am**

Hi all,

I am trying to convert the get the quarter for Outlink.field1 column from the Inlink.field1 having timestamp in the format YDM and my code for the conversion is as follows

Oconv(Iconv(TIMESTAMP.TO.DATE( DSLink2.field1),'YDM[4,2,2]'),'YMD[4,2,2]')

Job runs successfully throwing warnings but not writing any records in the Outlink.field1

SQLSTATE=23000, DBMS.CODE=1400
[DataStage][SQL Client][ODBC][DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-01400: cannot insert NULL into ("RELSTAGING"."STG_CDA_PRTL_PRD_DUMMY"."CURR_CYCLE_DATE")

scd1..STG_CDA_PRTL_PRD_DRC_AGN_OVACR_Trf (Timestamp) transform error: Invalid timestamp input '2008-02-12 00:00:00.000'

Your suggestions are welcomed to retrieve this

thanks in advance

Posted: **Thu Aug 14, 2008 4:50 am**

converting to get the quarter field

Posted: **Thu Aug 14, 2008 6:21 am**

Code: Select all

Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")

Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):

Code: Select all

QUARTER.TAG(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"))

Posted: **Thu Aug 14, 2008 6:46 am**

ray.wurlod wrote:

Code: Select all

Oconv(Iconv(DSLink2.Field1,"DYMD"),"DQ")

Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):

Code: Select all

QUARTER.TAG(Iconv(DSLink2.Field1, ...[/quote]

Thanks ray

As I am coding as quoted like, Oconv(Iconv(DSLink2.,"DYMD"),"DQ")

job runs successfully without warnings (since i alter the target oracle table modifying NOTNULL  to allow NULL) but the field1 is NULL..

actual datatype of Inlink.Field1 is Timestamp(23,3) so the data is '2008-02-12 00:00:00.000'  and the Outlink.Field1 is Timestamp(3,3)

Whether it could be the problem of getting null values ?
I could not get in to the problem identification.  

any suggestions are welcomed

Thanks in advance

Posted: **Thu Aug 14, 2008 7:14 am**

As Ray notes, substring the date out then convert. IMHO, neither field should be declared a Timestamp in your job as neither fits that definition, use Varchar for the source and decimal/integer/numeric for the target.

Posted: **Thu Aug 14, 2008 3:41 pm**

pxraja wrote:

ray.wurlod wrote:

Code: Select all

Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")

Or, if you want an ISO 8601 "quarter tag" format (YYYYQn):

Code: Select all

QUARTER.TAG(Iconv(Field(DSLink2.Field1," ",1,1), ...[/quote]

Thanks ray

As I am coding as quoted like, Oconv(Iconv(DSLink2.,"DYMD"),"DQ")

job runs successfully without warnings (since i alter the target oracle table modifying NOTNULL  to allow NULL) but the field1 is NULL..

actual datatype of Inlink.Field1 is Timestamp(23,3) so the data is '2008-02-12 00:00:00.000'  and the Outlink.Field1 is Timestamp(3,3)

Whether it could be the problem of getting null values ?
I could not get in to the problem identification.  

any suggestions are welcomed

Thanks in advance[/quote]

Posted: **Sun Aug 17, 2008 10:55 pm**

ray.wurlod wrote:
pxraja wrote:
ray.wurlod wrote:
Code: Select all
Oconv(Iconv(Field(DSLink2.Field1," ",1,1),"DYMD"),"DQ")
Or, if you want an ISO 8601 "quarter t ...

Hi all,

Thanks for your ideas and suggestions, the above quote has resolved the problem

DSXchange

converting timestamp to date format

converting timestamp to date format