Auto correct

dataservices rapidmarts
1

Hi,

I am getting the below error for the autocorrection step in inventory rapidmart. The primary key is plant_id+storage_locatn_id+material_id+valid_from_date. The company code is the following column next to primary key. I guess somehow the record’s remaining columns are getting read.

20441 9 DBS-070300 10/1/2012 12:57:47 PM |Sub data flow Copy_1_DF_InventoryFactDeltaUpdate_SAP_2|Reader SelectQuery
20441 9 DBS-070300 10/1/2012 12:57:47 PM SQL submitted to Oracle Server resulted in error <ORA-01400: cannot insert NULL into
20441 9 DBS-070300 10/1/2012 12:57:47 PM (“INV_MART_DATA”.“INVENTORY_FACT_Test”.“CMPNY_CODE_ID”)
20441 9 DBS-070300 10/1/2012 12:57:47 PM >. The SQL submitted is <MERGE INTO “INV_MART_DATA”.“INVENTORY_FACT_Test” s
20441 9 DBS-070300 10/1/2012 12:57:47 PM USING
20441 9 DBS-070300 10/1/2012 12:57:47 PM (SELECT “DXFER_INVENTORY_FACT”.“PLANT_ID” PLANT_ID , “DXFER_INVENTORY_FACT”.“STORAGE_LOCATN_ID” STORAGE_LOCATN_ID ,
20441 9 DBS-070300 10/1/2012 12:57:47 PM “DXFER_INVENTORY_FACT”.“MATERIAL_ID” MATERIAL_ID , “DXFER_INVENTORY_FACT”.“VALID_FROM_DATE” VALID_FROM_DATE ,
20441 9 DBS-070300 10/1/2012 12:57:47 PM (to_date(to_char( to_date(‘2012.10.01 12:43:33’, ‘yyyy.mm.dd hh24:mi:ss’) , ‘YYYY.MM.DD’), ‘YYYY.MM.DD’) - 1) VALID_TO_DATE , ’
20441 9 DBS-070300 10/1/2012 12:57:47 PM ’ CURRENT_FLAG
20441 9 DBS-070300 10/1/2012 12:57:47 PM FROM “INV_MART_DATA”.“DT__69_303_1_1” “DXFER_INVENTORY_FACT”
20441 9 DBS-070300 10/1/2012 12:57:47 PM WHERE ( “DXFER_INVENTORY_FACT”.“VALID_TO_DATE” = to_date(‘9999.12.31 00:00:00’, ‘yyyy.mm.dd hh24:mi:ss’) ) and (
20441 9 DBS-070300 10/1/2012 12:57:47 PM “DXFER_INVENTORY_FACT”.“CURRENT_FLAG” = ‘X’)
20441 9 DBS-070300 10/1/2012 12:57:47 PM ORDER BY “DXFER_INVENTORY_FACT”.“PLANT_ID” ASC , “DXFER_INVENTORY_FACT”.“STORAGE_LOCATN_ID” ASC ,
20441 9 DBS-070300 10/1/2012 12:57:47 PM “DXFER_INVENTORY_FACT”.“MATERIAL_ID” ASC , “DXFER_INVENTORY_FACT”.“VALID_FROM_DATE” ASC
20441 9 DBS-070300 10/1/2012 12:57:47 PM ) n
20441 9 DBS-070300 10/1/2012 12:57:47 PM ON ((s.PLANT_ID = n.PLANT_ID) AND
20441 9 DBS-070300 10/1/2012 12:57:47 PM (s.STORAGE_LOCATN_ID = n.STORAGE_LOCATN_ID) AND
20441 9 DBS-070300 10/1/2012 12:57:47 PM (s.MATERIAL_ID = n.MATERIAL_ID) AND
20441 9 DBS-070300 10/1/2012 12:57:47 PM (s.VALID_FROM_DATE = n.VALID_FROM_DATE))
20441 9 DBS-070300 10/1/2012 12:57:47 PM WHEN MATCHED THEN
20441 9 DBS-070300 10/1/2012 12:57:47 PM UPDATE SET s.“VALID_TO_DATE” = n.VALID_TO_DATE,
20441 9 DBS-070300 10/1/2012 12:57:47 PM s.“CURRENT_FLAG” = n.CURRENT_FLAG
20441 9 DBS-070300 10/1/2012 12:57:47 PM WHEN NOT MATCHED THEN
20441 9 DBS-070300 10/1/2012 12:57:47 PM INSERT /*+ APPEND */ (s.“PLANT_ID”, s.“STORAGE_LOCATN_ID”, s.“MATERIAL_ID”, s.“VALID_FROM_DATE”, s.“VALID_TO_DATE”,
20441 9 DBS-070300 10/1/2012 12:57:47 PM s.“CURRENT_FLAG” )
20441 9 DBS-070300 10/1/2012 12:57:47 PM VALUES (n.PLANT_ID , n.STORAGE_LOCATN_ID , n.MATERIAL_ID , n.VALID_FROM_DATE , n.VALID_TO_DATE , n.CURRENT_FLAG)
20441 9 DBS-070300 10/1/2012 12:57:47 PM >.
21734 9 FIL-080134 10/1/2012 12:57:47 PM |Dataflow Copy_1_DF_InventoryFactDeltaUpdate_SAP|Pipe Listener for Copy_1_DF_InventoryFactDeltaUpdate_SAP_2
21734 9 FIL-080134 10/1/2012 12:57:47 PM Named pipe error occurred:
21699 1 FIL-080134 10/1/2012 12:57:50 PM |Dataflow Copy_1_DF_InventoryFactDeltaUpdate_SAP|Pipe Listener for Copy_1_DF_InventoryFactDeltaUpdate_SAP_2
21699 1 FIL-080134 10/1/2012 12:57:50 PM Named pipe error occurred:

arjunria25 (BOB member since 2011-09-30)

2

Can you add some nvl() functions to the primary key columns? It seems one of them is null which is allowed in SAP but not in Oracle.

Werner Daehn :de: (BOB member since 2004-12-17)

3

Thanks Werner! I shall test it and will let you know. Meanwhile, I ran the Job by splitting the data into smaller subset and the job run was successful. Still cannot understand how the Constraint voilation was fixed by splitting the data load in smaller subset.

arjunria25 (BOB member since 2011-09-30)