Skip to content


Document Task SQL Command

Brandon asked 7 years ago
I am going to do my best to explain my issue, hopefully someone can assist me.
I am using SmartConnect to read data from a table.  It successfully updates the customer record table, then my task, listed under “Tasks that run if the document succeeds” is supposed to either, update EFT information if information already exists in the SY06000 table, or insert into it if it doesn’t.  I know that inserting into the SY06000 table through SQL isn’t a good idea, but this is what I have been asked to do.
My question is, how can I get my SQL query to only work with the one row that smartconnect is currently processing? I think what is happening is my query is reading from the source table the first record, and necessarily the one that SC is working on.
I hope this makes sense.  I may be having a hard time explaining it.  I have pasted my query below.  THANK YOU for any assistance.
–copy bank information from ZDA table into SY06000——————————————–
if exists (select * from test..SY06000 where CUSTNMBR= ‘_CustomerNumber’)
  update test..sy06000
  set Bankname = ZDABankName,
      EFTTransitRoutingNO = ZDARoutingNumber,
 EFTBankAcct = ZDABankAccountNumber
  from zda..CustomerRegistrations
  where CUSTNMBR= ‘_CustomerNumber’
  insert into TEST..SY06000 (CUSTNMBR, BANKNAME, EFTTransitRoutingNo, EFTBankAcct, SERIES, CustomerVendor_ID, ADRSCODE, EFTBankType, EFTUseMasterID, CURNCYID)
  select CustomerNumber, ZDABankName, ZDARoutingNumber, ZDABankAccountNumber, ‘3’, CustomerNumber, ‘PRIMARY’, ’31’, ‘1’, ‘US’
  From zda..CustomerRegistrations
Patrick Roth Staff answered 7 years ago
The value of _CustomerNumber should be the value of the current record you are working with – or more specifically the last record that SC read when it was processing and finished. 
If that wasn’t it, the only thing I can think of is that you use a global variable and then on the Restrictions node in your mapping you can set that global to the customer number.
Then in your SQL task, reference the global instead of the column name.
Jed replied 4 months ago

I have a question in a similar vein to this one. I have a SmartConnect SOP integration that read from SQL table TruckingData. Can the Pre-Document SQL task insert a record into that same table, TruckingData, and will it be picked up for integration? I need to create a 2nd SOP line based on the 1st one.

If you would like to submit an answer or comment, please sign in to the eOne portal.