DSXchange

i have a field in my job which is in the format 2006-11-30-10.28.15.817238 and the data type for this field is Char(26)
and the field in Oracle table is of the format 2006-12-01 16:23:11 and the data type is Timestamp
well i have formatted my field in the job to the format in oracle by removing the milli seconds from the field and trimming the field .
now it is in the same format as oracle . but it is not loading into the oracle table.
does anybody knows what the problem could be ?

What is the length of the Timestamp field? It should be 38. Try it.

John

well the length is 38 itself .
is there ant other problem that you could think about ?

Welcome Aboard! :D

What is the logic and code that you used to convert the original timestamp into the Oracle timestamp?

Also, it would be helpful to provide the error messages of the job run.

Rather than posting messages like below from which zilch can be inferred.

sshettar wrote:
but it is not loading into the oracle table.

Thanks,
Whale.

Check you MM and DD formats ! may be it got swaped by mistake .

Try loading to a sequential file first and see whether , really, you got the conversion successful. Most of the time in such situations, the blank space between the date part and time part of the timestamp is missing. Also, if you post the derivation, it would help us pin point the mistake, if there is any.

well as i already mentioned the filed i want to load in oracle is of the format 2006-11-30-10.28.15.817238 and of type char (26)
i formatted this field in the follwoing way
Left(l_to_Tform.BMC_TS,19)
Trim(DateFormat,"-","A")
Trim(TrimD1,".","A")
TrimD2 [1,4] :'-' : TrimD2 [5,2] :'-' : TrimD2 [7,2] :' ' : TrimD2 [9,2] :':' : TrimD2 [11,2] :':' : TrimD2 [13,2]

now the field in the oracle is of this format 2006-12-04 14:26:24 and of type Timestamp

well the job is running succesfully but no rows loading into the oracle due to this field

the warning messages are as follows

ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: The value of the row is: PROCESS_ID = 2 SUBPROCESS_ID = 17 MEASUREMENT_ID = 50 PROCESS_TIMESTAMP = 2006-11-30 10:28:33 SUBPROCESS_TIMESTAMP = 2006-12-04 14:26:23 MEASUREMENT_TIMESTAMP = 2006-12-04 14:26:24 MEASUREMENT_VALUE = 39 MEASUREMENT_RESULT = PASS


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


ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: DBMS.CODE=ORA-00001


ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: The value of the row is: PROCESS_ID = 2 SUBPROCESS_ID = 17 MEASUREMENT_ID = 52 PROCESS_TIMESTAMP = 2006-11-30 10:28:33 SUBPROCESS_TIMESTAMP = 2006-12-04 14:26:23 MEASUREMENT_TIMESTAMP = 2006-12-04 14:26:24 MEASUREMENT_VALUE = 39 MEASUREMENT_RESULT = PASS


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


ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: DBMS.CODE=ORA-00001


ETRExtractMerchContractRulesRLWithOracleEntry..Transformer_11: DSD.INTClose Active stage finishing.
1 rows read from I_SBN_Merch_Contr_Rules_RCount
1 rows written to I_SBN_Merch_Contr_Rules_Valid_Sub_TXN
0 rows written to I_ETL_CONTRACT_RL_COUNT
0 rows written to I_ETL_CONTRACT_RL_SELECT_COUNT

first 6 are warnings and 7th one is an Info

well i have removed all primary keys in the table . none of the fields are primary key

but still the waring messages remain the same

i hope i have given all the nessary information.

i would really appreciate your help with this issue

Thanks
shettar

well i have tried loading into sequential file and the conversion looks fine .
Any other suggestions?

You're correct in your conversion of the timestamp. What makes you think it is in any way related to your constraint violation problem?

You need to look at the definition of the constraint being violated - like ETQ.PK_VALIDATION_VALUES for example - and see what field(s) make up that constraint. All you know at this point is the fact that some combination of fields in each record must be unique and that isn't happening. Focusing on the format of the timestamp is a Red Herring.

Note that the violation can happen not only from an Insert but that an Update could cause it as well!

well i tried all possible combinations of primary keys . but still it doesn't work . well i have
process_id
subprocess_id
measurement_id
processtimestamp
subprocesstimestamp
measurementtimestamp
measurement_value
measurement_result
of which most of the fields have values hardcoded
it is just the process timestamp field and measurement_value field which gets the value depending on the job
the subprocesstimestamp gets the value from getjobstarttimestamp
and measurementtimestamp gets the value from curent timestamp.
well curernt timestamp was initially not in the format of the timestamp as oracle ,so i made the changes and that works
but it is only with the processtimestamp that even after all conversion and formatting it doesnt work

Could somebody help me with this issue

Can you list the constraints on your target table?

Whale.

This is getting crazy. Are you and beaditya the same person? You both seem to be working on the exact same problem, same table, same PK violation, same confusion - same everything.

Can the two of you get together and start a single new thread where your remaining issue is clearly defined now that all the other silliness is out of the way? Sheesh.

ps. Yes, I purposefully posted this in both threads.

My two cents !

I have seen this happening with other users as well.

Same issue being posted under different user_names.

AND THAT's NOT A GOOD WAY TO GO !!

sshettar wrote:well i tried all possible combinations of primary keys . but still it doesn't work .

Just change the update action to 'Update existing rows only'

Go to your DBA. Get him/her involved. It doesnt make sense that even after dropping all the primary keys your getting this error. See what he/she has to say.