Skip to content
+1-888-319-3663

COMMUNITY FORUM

SmartConnect 2013 Tasks

john.ellis@tribridge.com asked 8 years ago
Hello:

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.

Help!

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'

WHEN

UPR00400.PAYRCORD <> 'SALY' AND UPR00400.EMPLOYID <> 'ELLI0001' THEN ''

END

FROM UPR00100 INNER JOIN UPR00400 ON UPR00100.EMPLOYID = UPR00400.EMPLOYID

Lorren replied 8 years ago

John,

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.

SELECT '_MYSOURCECOLUMN'

Answers
Best Answer
johnellis answered 8 years ago
Thanks, Lorren!

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