DSXchange

Im getting 6 digits dae (980501) and there are even 0 in the source so I need to convert the date in the Target as (MM-DD-YYYY) and store also if its 0 I need to load it as null. The src datatype is DECIMAL and the target data type is DATE So i have written If In_PMH_TRANS_DT = 0 Then @NULL Else ( I...

Thanks for replying. I check the date format and its all with yyyy-mm-dd and I have tried the syntax that was posted.

Now I get the error ORA-01861: literal does not match format string

oconv(iconv(In_MTRANS_DT,'d4'),'d-ymd[4,2,2]')

Im getting the date in the format yyyy-mm-dd and I want to be loaded in the target in the format mm-dd-yyyy. I tried using Iconv and oconv but its saying its not valid month may be the syntax im writing is not right. Can anyone plz help me with the syntax to convert date format yyyy-mm-dd to mm-dd-y...

Thanks for your reply arun. I just saw the format in the output oracle stage. The format is TO_DATE(:7, 'DD-MON-YY'). This is generated sql and I want to change as user defined sql but the tab is grayed out. How can I do that. Also I was going through the server job guide and if the Oconv or Iconv f...

Thanks for your reply arun. I just saw the format in the output oracle stage. The format is TO_DATE(:7, 'DD-MON-YY'). This is generated sql and I want to change as user defined sql but the tab is grayed out. How can I do that. Also I was going through the server job guide and if the Oconv or Iconv f...

Thank you so much for your time and help. I have just tried using If In_RCS_PIRPRDDBL_OPMPREP.MPPHDT = '0' Then @NULL Else oconv(iconv(In_RCS_PIRPRDDBL_OPMPREP.MPPHDT,'d4'),'d-ymd[4,2,2]') and the to_date is already used in the target table still Im getting the error "Output", while proces...

Oracle is the target database.

Can I do this conversion using Iconv,Oconv. If so can you plz let me know the syntax.

I have written a function like this If Input.a = 0 Then @NULL Else ( If (Input.a [1, 1]<='2') Then '20' : Input.a [2, 2] : '-' : Input.a [4, 2] : '-' : Input.a [6, 2] Else '19' : Input.a [1, 2] : '-' : Input.a [3, 2] : '-' : Input.a [5, 2]) but still Im getting the error "Attempt to convert Str...

The source is AS400 and the date column has the datatype as decimal and its coming as either 0's or 950401(YYMMDD). I tried using Iconv Oconv and also DateTimeStampToODBC routine still im getting the error Attempt to convert String value "0--" to Date type unsuccessful. I appreciate any in...

Im using a select query in the transformer using a stage variable to get the dt.The query I have written works in Toad but in data stage its giving me an error ORA-00933: SQL command not properly ended. Here is the query im using in the transformer SELECT count(*)FROM dw.frm_fact gff,dw.frm_cmp_dim ...

vinothkumar wrote:Do a exact search on the error message in this forum.

I have already tried that and there was one msg but there was no answer provided

SP stage can be used and to get count get rowcount using transformes Thank you so much for the response. I have used the STP but Im getting the error Stored_Procedure_34: First output column must be a Procedure return code. I'm not sure which option Im not specifying it right. Im following the Data...

Im in the initial stage of learning datastage. I have a stored procedure which takes input as a date.Data_out is a cursor type. In datastage I just need to get the counts of those out records of the result set of the stored procedure. Can I use the ODBC stage or STP stage. I have this doubt bcoz doc...

This worked once I removed the quotes for the ITM_CD now its adding 0 to all 3 digits ITM_CD

if Len(Pri_GPRS_Unt_Sls.ITM_CD)<=3 then '0' : Trim(Upcase(Pri_GPRS_Unt_Sls.ITM_CD)) else Trim(Upcase(Pri_GPRS_Unt_Sls.ITM_CD))

Thanks for the information