Document Task SQL Command
Brandon asked 7 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
———————————————————————————————–
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 7 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
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
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.