Skip to content


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?
Patrick Roth Staff answered 4 years ago
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.
Chris answered 4 years ago
Thank you! 
That makes sense for the errors now.

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