Help with "timestamp" for OCI-9

[ady]

I am loading data into a OCI-9 stage and I have 3 timestamp fields in that OCI table. In the 1st one we are giving the macro "DSJobStartTimestamp", The second one We are giving the current timestamp as OCONV(DATE(), "D4-YMD[4,2,2]") : " " : OCONV(TIME(), "MTS") it gives the date output as "2006-12-01 12:15:51".

For the 3rd timestamp field we have the source timestamp( this actually is "CHAR" datatype in a complex flat file) in the form of "2006-11-30-10.28.24.942" we need this timestamp to look as "2006-11-30 10:28:24". We need to delete the microseconds as our OCI table is not accepting it. to do this I am using 4 stagevariables.

Left(input_column,19) = DF

Trim(DF,"-","A") = D1

Trim(TrimD1,".","A") = D2

TrimD2 [1,4] :'-' : TrimD2 [5,2] :'-' : TrimD2 [7,2] :' ' : TrimD2 [9,2] :':' : TrimD2 [11,2] :':' : TrimD2 [13,2]


and we are getting the date succesfully in the format "2006-11-30 10:28:24". But DS does not write this to the OCI table but it writes the date from the OCONV function I mentioned above in RED.

can someone tell me why ?

[DSguru2B]

Maybe that particular column's derivation is equivalent to what you have specified in red. [shrug]
How are you doing your third timestamp parsing. Is it via a routine. Make sure you are calling that routine in the third timestamp's derivation.

[ady]

I am using all the 4 statements mentioned as stage variables and giving the final output to the column where I need the timestamp.

[I_Server_Whale]

How are you doing your third timestamp parsing. Is it via a routine.

The third timestamp is being calculated by using stage variables.

Instead of using a long winded logic :

Code: Select all

Left(input_column,19) = DF 

Trim(DF,"-","A") = D1 

Trim(TrimD1,".","A") = D2 

TrimD2 [1,4] :'-' : TrimD2 [5,2] :'-' : TrimD2 [7,2] :' ' : TrimD2 [9,2] :':' : TrimD2 [11,2] :':' : TrimD2 [13,2 ] 

You could have achieved the same with

Code: Select all

input_col[1,10]:' ':input_col[12,2]:':':input_col[15,2]:':':input_col[18,2]

in your derivation, if you are sure that your input_col will always be 23 characters in length.

Note: It is a single space between the single quotes in the above code.

When you were trying write this value, what was error message that was generated. It would help if you could post the error message from the log.

Whale.

[ady]

Note: It is a single space between the single quotes in the above code.

I know thats a very long winded logic but the thing is I also need to trim the "." and give ":" in their place.

[I_Server_Whale]

I just realized that and corrected the code. See previous post.

[DSguru2B]

Thats not long winded.
This is long winded

Code: Select all

Left(in.Col,10):' ':Ereplace(Left(Right(in.Col,12),8),".",":")

[I_Server_Whale]

But DS does not write this to the OCI table but it writes the date from the OCONV function I mentioned above in RED.

can someone tell me why ?

What is the error message when you try to insert the calculated value?

Thanks,
Whale.

[I_Server_Whale]

Thats not long winded.
This is long winded

Code: Select all

Left(in.Col,10):' ':Ereplace(Left(Right(in.Col,12),8),".",":")

That's much better one !!

How about this?

Code: Select all

In.Col[1,10]:' ':Ereplace(In.Col[12,8],".",":")

Whale.

[ady]

There are no fatal errors, it just shows that o rows have been transferred.

There are warnings though !




TESTBMC2..Transformer_11: DBMS.CODE=ORA-01850


TESTBMC2..Transformer_11: The value of the row is: PROCESS_ID = 2 SUBPROCESS_ID = 17 MEASUREMENT_ID = 50 PROCESS_TIMESTAMP = 2006-11-30 28:33:99 SUBPROCESS_TIMESTAMP = 2006-12-01 15:00:17 MEASUREMENT_TIMESTAMP = 2006-12-01 15:00:18 MEASUREMENT_VALUE = 39 MEASUREMENT_RESULT = PASS



TESTBMC2..Transformer_11: ORA-01850: hour must be between 0 and 23




Each of the above warnings are issued twice. The column in RED is the one where we are writing this value into.

[ady]

Thats not long winded.
This is long winded

Code: Select all

Left(in.Col,10):' ':Ereplace(Left(Right(in.Col,12),8),".",":")

That's much better one !!

How about this?

Code: Select all

In.Col[1,10]:' ':Ereplace(In.Col[12,8],".",":")

Whale.

NICE !!!!

[I_Server_Whale]

PROCESS_TIMESTAMP[/color] = 2006-11-30 28:33:99



TESTBMC2..Transformer_11: ORA-01850: hour must be between 0 and 23

The hour is 28 that you are trying to insert, but valid values are 0 to 23.

Check if the derivation is getting the right values by dumping into sequential file. The hour must be between 0 to 23.

Thanks,
Whale.

[ray.wurlod]

You've got the character positions misaligned. For example the month portion should be TrimD2[6,2], not TrimD2[5,2]. You didn't allow for the delimiter character.

[ady]

I have changed the statement into

Left(I_SBN_Merch_Contr_Rules.BMC_TS,10):' ':Ereplace(Left(Right(I_SBN_Merch_Contr_Rules.BMC_TS,15),8),".",":")


and also (6,2) as Ray mentioned. But now the job gives a new Warning:



TESTBMC2..Transformer_11: ORA-00001: unique constraint (ETQ.PK_VALIDATION_VALUES) violated



The table i'm inserting into is "ETQ.VALIDATION_VALUES"

[ady]

GOOGLE SAYS:


This error is issued if a user tries to insert an already existing value into a column defined unique.




Cause:
This error means that an attempt has been made to insert a record with a duplicate (unique) key. This error will also be generated if an existing record is updated to generate a duplicate (unique) key. Typically this is a duplicate primary key, but it need not be the primary key.

Remedy:
Only one of the following will be appropriate:

Remove the unique restriction.

Change the restriction to allow duplicate keys. An index could be changed to be a non-unique index, but remember that the primary key must always be unique.

Do not insert the duplicate key.

Usually this error indicates an application error or error on the part of the user.




I"m gonna look into the "Keys"