Skip to Content
avatar image
Former Member

Select and update issue occuring when using JDBC sender channel

All,

We are doing a select statement followed by an update statement.

After the select of Top 500 records, The update statement updates the top 500 that was selected previously with ICC_Date_time.with current dateso that it is not picked by the next select in the next run.

Here are the following statements

SELECT dbo.SAP_Customer_One_2_One.Record_Key as Header_Record_Key, dbo.SAP_Customer_One_2_One.Client as Header_Client, dbo.SAP_Customer_One_2_One.Sales_Org as Header_Sales_Org, dbo.SAP_Customer_One_2_One.Company_Code as Header_Company_Code, dbo.SAP_Customer_One_2_One.SAPID as Header_SAPID, dbo.SAP_Customer_One_2_One.Party_ID as Header_Party_ID, dbo.SAP_Customer_One_2_One.Party_AddressID as Header_Party_AddressID, dbo.SAP_Customer_One_2_One.AddressID as Header_AddressID, dbo.SAP_Customer_One_2_One.Dept_ID as Header_Dept_ID, dbo.SAP_Customer_One_2_One.Org_Name as Header_Org_Name, dbo.SAP_Customer_One_2_One.ADDRESS_LINE_1 as Header_ADDRESS_LINE_1, dbo.SAP_Customer_One_2_One.ADDRESS_LINE_2 as Header_ADDRESS_LINE_2, dbo.SAP_Customer_One_2_One.CITY_NAME as Header_CITY_NAME, dbo.SAP_Customer_One_2_One.STATE_CD as Header_STATE_CD, dbo.SAP_Customer_One_2_One.POSTAL_CD as Header_POSTAL_CD, dbo.SAP_Customer_One_2_One.POSTAL_EXT_CD as Header_POSTAL_EXT_CD, dbo.SAP_Customer_One_2_One.COUNTY_NAME as Header_COUNTY_NAME, dbo.SAP_Customer_One_2_One.COUNTRY_CD as Header_COUNTRY_CD, dbo.SAP_Customer_One_2_One.PO_BOX as Header_PO_BOX, dbo.SAP_Customer_One_2_One.PO_BOX_POSTAL_CD as Header_PO_BOX_POSTAL_CD, dbo.SAP_Customer_One_2_One.GLN as Header_GLN, dbo.SAP_Customer_One_2_One.CUST_LEGAL_NAME as Header_CUST_LEGAL_NAME, dbo.SAP_Customer_One_2_One.DOING_BUSINESS_AS as Header_DOING_BUSINESS_AS, dbo.SAP_Customer_One_2_One.INDUSTRY_CHANNEL_CD as Header_INDUSTRY_CHANNEL_CD, dbo.SAP_Customer_One_2_One.INDUSTRY_SEGMENT_CD as Header_INDUSTRY_SEGMENT_CD, dbo.SAP_Customer_One_2_One.NAICS_PRIMARY as Header_NAICS_PRIMARY, dbo.SAP_Customer_One_2_One.GOVERNMENT_TYPE as Header_GOVERNMENT_TYPE, dbo.SAP_Customer_One_2_One.APPLIED_SCIENCE_CD as Header_APPLIED_SCIENCE_CD, dbo.SAP_Customer_One_2_One.CONSIG_PROGRAM as Header_CONSIG_PROGRAM, dbo.SAP_Customer_One_2_One.CUSTOMER_LAB_CAT as Header_CUSTOMER_LAB_CAT, dbo.SAP_Customer_One_2_One.BUILD_NUM as Header_BUILD_NUM, dbo.SAP_Customer_One_2_One.FLOOR as Header_FLOOR, dbo.SAP_Customer_One_2_One.SUITE as Header_SUITE, dbo.SAP_Customer_One_2_One.MAIL_STOP as Header_MAIL_STOP, dbo.SAP_Customer_One_2_One.Department as Header_Department, dbo.SAP_Customer_One_2_One.Division as Header_Division, dbo.SAP_Customer_One_2_One.Rudid as Header_Rudid, dbo.SAP_Customer_One_2_One.Sug_Acct_Gp as Header_Sug_Acct_Gp, dbo.SAP_Customer_One_2_One.CSR_Prompt as Header_CSR_Prompt, dbo.SAP_Customer_One_2_One.Text_1 as Header_Text_1, dbo.SAP_Customer_One_2_One.Text_2 as Header_Text_2, dbo.SAP_Customer_One_2_One.Cust_Mast_Process_URL as Header_Cust_Mast_Process_URL, dbo.SAP_Customer_One_2_One.Search_Term as Header_Search_Term, dbo.SAP_Org_Alt_ID.ALT_ID, dbo.SAP_Org_Alt_ID.ALT_ID_TYPE, dbo.SAP_Org_Alt_ID.ALT_ID_VALUE, dbo.SAP_Org_Ecom.Ecomm_ID, dbo.SAP_Org_Ecom.Ecomm_type, dbo.SAP_Org_Ecom.Ecomm_Value, dbo.SAP_Org_Phone.Phone_ID, dbo.SAP_Org_Phone.Phone_Type, dbo.SAP_Org_Phone.Phone_Value, dbo.SAP_Org_Phone.PHONE_EXT_NUM FROM (SELECT  TOP 500  Record_Key, ICC_Date_Time, End_Date_Time, Transfer_Type FROM dbo.SAP_Transfer_Batch_Number  with (UPDLOCK, NOWAIT) WHERE Transfer_Type = 'Customer' AND End_Date_Time IS NOT NULL AND ICC_Date_Time IS NULL order  by   dbo.SAP_Transfer_Batch_Number.Record_Key) Batch INNER JOIN dbo.SAP_Customer_One_2_One ON Batch.Record_Key = dbo.SAP_Customer_One_2_One.Record_Key LEFT OUTER JOIN dbo.SAP_Org_Alt_ID ON Batch.Record_Key = dbo.SAP_Org_Alt_ID.Record_Key LEFT OUTER JOIN dbo.SAP_Org_Ecom ON Batch.Record_Key = dbo.SAP_Org_Ecom.Record_Key LEFT OUTER JOIN dbo.SAP_Org_Phone ON Batch.Record_Key = dbo.SAP_Org_Phone.Record_Key ORDER BY Batch.Record_Key

Update:

UPDATE dbo.SAP_Transfer_Batch_Number  SET ICC_Date_Time = GETDATE() WHERE Record_Key IN   (SELECT TOP 500 Record_Key FROM dbo.SAP_Transfer_Batch_Number   WHERE Transfer_Type = 'Customer'  AND End_Date_Time IS NOT NULL AND  ICC_Date_Time IS NULL   ORDER BY dbo.SAP_Transfer_Batch_Number.Record_Key)

Issue

We are missing at random batches of 500 in a day when this is executed in SAP PI. At times update is executed before the select is triggered

1-500 dropped
501-2500 processed
2501-3000 dropped
3001-3500 processed
3501-4000 dropped
4001-4060 processed

Is there any better approach to do this without missing the batches of 500 in SAP PI JDBC sender channel?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • May 19, 2015 at 12:41 PM
    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Inaku Vila

      We are doing exactly the same method of selecing TOP 500 records and updating the same 500 records.

      Also the Transaction Isolation Level is set to Serializable only.