SQL task to conditionally insert row
Chris asked 4 years ago
What I have so far is:
IF _Zelle = ‘Y’
insert into sy06000 (SERIES,CustomerVendor_ID,ADRSCODE,VENDORID,EFTUseMasterID,EFTBankType,FRGNBANK,INACTIVE,GIROPostType, EFTBankCode,BankInfo7,CURNCYID,EFTTransferMethod,EFTAccountType) VALUES (4,’_Tempvendor’,’MAILING’,’_Tempvendor’,1,31,0,0,0,’ZEL’,0,’US’,1,1)
The insert on it’s own works fine. With the “if” added in I get “Invalid column name ‘Y’.” for a row with a “Y” for _Zelle and “Incorrect syntax near ‘=’.” when _Zelle is blank. The SQL statement with the “if” works outside of SmartConnect. Any idea what’s going on?
IF _Zelle = ‘Y’
insert into sy06000 (SERIES,CustomerVendor_ID,ADRSCODE,VENDORID,EFTUseMasterID,EFTBankType,FRGNBANK,INACTIVE,GIROPostType, EFTBankCode,BankInfo7,CURNCYID,EFTTransferMethod,EFTAccountType) VALUES (4,’_Tempvendor’,’MAILING’,’_Tempvendor’,1,31,0,0,0,’ZEL’,0,’US’,1,1)
The insert on it’s own works fine. With the “if” added in I get “Invalid column name ‘Y’.” for a row with a “Y” for _Zelle and “Incorrect syntax near ‘=’.” when _Zelle is blank. The SQL statement with the “if” works outside of SmartConnect. Any idea what’s going on?
Answers
Patrick Roth Staff answered 4 years ago
Chris,
From a SC perspective, all SC does is replace your field with the data – it doesn’t try to otherwise format any of your coding.
So in this case, you were very close. The issue is the single quotes around the first field of your condition are missing. So the statement executes as:
if N = ‘Y’
which is incorrect since the N isn’t a literal and thus “must” be a column from a SQL perspective and so you’d get the invalid column. Same then with a blank _Zelle because then the field is missing altogether – so a syntax error.
If ‘_Zelle’ = ‘Y’
by adding in the single quotes, we have literal = literal and we would be good.
patrick
From a SC perspective, all SC does is replace your field with the data – it doesn’t try to otherwise format any of your coding.
So in this case, you were very close. The issue is the single quotes around the first field of your condition are missing. So the statement executes as:
if N = ‘Y’
which is incorrect since the N isn’t a literal and thus “must” be a column from a SQL perspective and so you’d get the invalid column. Same then with a blank _Zelle because then the field is missing altogether – so a syntax error.
If ‘_Zelle’ = ‘Y’
by adding in the single quotes, we have literal = literal and we would be good.
patrick