SmartConnect 2013 Tasks
I'm trying to get my very simple SmartConnect map for Employee Pay Code Maintenance to check the box for "Primary Pay Code" if the imported pay code is a salary code.
So, I created a task containing a SQL script to run if the document succeeds. It's not working, for two reasons.
First, it keeps complaining about the variables (source columns) I try to use. it keeps saying that a column does not exist. It exists.
So, I eliminated the variables and hard-coded the values in even though I don't like to have to do that.
Now, it still won't run the task and import, because it keeps saying that a null value is not allowed in the Primary_Pay_Record field of UPR00100.
Here is the SQL script for the task:
UPDATE UPR00100 SET Primary_Pay_Record = CASE WHEN UPR00400.PAYRCORD = 'SALY' AND UPR00400.EMPLOYID = 'ELLI0001' THEN 'SALY'
UPR00400.PAYRCORD <> 'SALY' AND UPR00400.EMPLOYID <> 'ELLI0001' THEN ''
FROM UPR00100 INNER JOIN UPR00400 ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
You always need an ELSE when using a CASE statement in SQL so that when it falls through your other conditions it has something to set, thus your NULL value being set.
For using source columns in SQL you need to put single quotes around it.