Help with "timestamp" for OCI-9
Moderators: chulett, rschirm, roy
Help with "timestamp" for OCI-9
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 ?
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 ?
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.
![Rolling Eyes :roll:](./images/smilies/icon_rolleyes.gif)
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
The third timestamp is being calculated by using stage variables.DSguru2B wrote: How are you doing your third timestamp parsing. Is it via a routine.
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 ]
Code: Select all
input_col[1,10]:' ':input_col[12,2]:':':input_col[15,2]:':':input_col[18,2]
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.
Last edited by I_Server_Whale on Fri Dec 01, 2006 1:46 pm, edited 2 times in total.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Thats not long winded.
This is long winded
This is long winded
![Wink :wink:](./images/smilies/icon_wink.gif)
Code: Select all
Left(in.Col,10):' ':Ereplace(Left(Right(in.Col,12),8),".",":")
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Re: Help with "timestamp" for OCI-9
What is the error message when you try to insert the calculated value?beaditya wrote: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 ?
Thanks,
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
That's much better one !!DSguru2B wrote:Thats not long winded.
This is long winded![]()
Code: Select all
Left(in.Col,10):' ':Ereplace(Left(Right(in.Col,12),8),".",":")
![Smile :)](./images/smilies/icon_smile.gif)
How about this?
Code: Select all
In.Col[1,10]:' ':Ereplace(In.Col[12,8],".",":")
![Wink :wink:](./images/smilies/icon_wink.gif)
Whale.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
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.
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.
Last edited by ady on Fri Dec 01, 2006 2:20 pm, edited 1 time in total.
I_Server_Whale wrote:That's much better one !!DSguru2B wrote:Thats not long winded.
This is long winded![]()
Code: Select all
Left(in.Col,10):' ':Ereplace(Left(Right(in.Col,12),8),".",":")
![]()
How about this?
Code: Select all
In.Col[1,10]:' ':Ereplace(In.Col[12,8],".",":")
Whale.
NICE !!!!
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
The hour is 28 that you are trying to insert, but valid values are 0 to 23.beaditya wrote:PROCESS_TIMESTAMP[/color] = 2006-11-30 28:33:99
TESTBMC2..Transformer_11: ORA-01850: hour must be between 0 and 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.
Anything that won't sell, I don't want to invent. Its sale is proof of utility, and utility is success.
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
Author: Thomas A. Edison 1847-1931, American Inventor, Entrepreneur, Founder of GE
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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"
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"
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"
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"