Skip to content
+1-888-319-3663

COMMUNITY FORUM

Document Task SQL Command

Brandon asked 5 years ago
Hello, 
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’
else  
  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
———————————————————————————————–  
Answers
Patrick Roth Staff answered 5 years ago
Brandon,
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.
patrick
eone
 

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