TBSupport
asked on
Cannot Insert Null Value into...
Hello:
Below is code that I'm using to update the field UPR00100.Primary_Pay_Code. SQL keeps throwing and error saying that it cannot insert the value NULL into that field. I just want the field to be empty if it doesn't meet the criteria of the if, then statement.
No, I cannot update the field to accept NULLs, etc. I need to find a way to fix the script.
I have tried COALESCE and ISNULL on that field with no success.
Any ideas?
Thanks!
TBSupport
UPDATE UPR00100 SET Primary_Pay_Record = CASE WHEN UPR00400.PAYRCORD = 'SALY' AND UPR00400.EMPLOYID = 'ELLI0001' THEN 1 WHEN UPR00400.PAYRCORD <> 'SALY' AND UPR00400.EMPLOYID <> 'ELLI0001' THEN 0 END FROM UPR00100 INNER JOIN UPR00400 ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
Below is code that I'm using to update the field UPR00100.Primary_Pay_Code.
No, I cannot update the field to accept NULLs, etc. I need to find a way to fix the script.
I have tried COALESCE and ISNULL on that field with no success.
Any ideas?
Thanks!
TBSupport
UPDATE UPR00100 SET Primary_Pay_Record = CASE WHEN UPR00400.PAYRCORD = 'SALY' AND UPR00400.EMPLOYID = 'ELLI0001' THEN 1 WHEN UPR00400.PAYRCORD <> 'SALY' AND UPR00400.EMPLOYID <> 'ELLI0001' THEN 0 END FROM UPR00100 INNER JOIN UPR00400 ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
ASKER
Thanks, JEaston, for the quick response! Unfortunately, the result was "0", when it should have been "1". That's because, indeed, there is a record in UPR00400 with ELLI0001 and SALY.
Can the script be modified to accommodate that?
TBSupport
Can the script be modified to accommodate that?
TBSupport
You don't need the 2nd WHEN condition. if the condition is satisified, 1 else 0.
UPDATE UPR00100
SET Primary_Pay_Record = CASE
WHEN UPR00400.PAYRCORD = 'SALY'
AND UPR00400.EMPLOYID = 'ELLI0001' THEN 1
ELSE 0
END
FROM UPR00100
INNER JOIN UPR00400
ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
What about if you only update rows which meet the condition? Or do you need to forcefully change it on every row?
ASKER
I tried the most recent script that I posted, here, and it didn't work either. It returned a value of "0" instead of "1", which it shouldn't do as I said earlier.
I'm not sure where to go, from here.
TBSupport
I'm not sure where to go, from here.
TBSupport
Can you post some sample data from your table which is not working
The code posted earlier by me (or the cleaner version from Sharath_123) should both return 1 when UPR00400.PAYRCORD = 'SALY' and UPR00400.EMPLOYID = 'ELLI0001'.
It is possible however that there is trailing spaces and therefore please try this:
If this doesn't work, as requested above please post some data from your table(s).
It is possible however that there is trailing spaces and therefore please try this:
UPDATE UPR00100
SET Primary_Pay_Record = CASE
WHEN LTRIM(RTRIM(UPR00400.PAYRCORD)) = 'SALY'
AND LTRIM(RTRIM(UPR00400.EMPLOYID)) = 'ELLI0001' THEN 1
ELSE 0
END
FROM UPR00100
INNER JOIN UPR00400
ON UPR00100.EMPLOYID = UPR00400.EMPLOYID
If this doesn't work, as requested above please post some data from your table(s).
ASKER
Good Morning:
Attached are two files showing the results of SQL scripting that I ran, per your request for sample data:
select * from UPR00100 where EMPLOYID = 'ELLIS0001'
select * from UPR00400 where EMPLOYID = 'ELLIS0001'
I ran the latest UPDATE statement in this "case", just now. These are the results when running select * from on these two tables.
Thanks! Much appreciated!
TBSupport
UPR00100.rpt
UPR00400.rpt
Attached are two files showing the results of SQL scripting that I ran, per your request for sample data:
select * from UPR00100 where EMPLOYID = 'ELLIS0001'
select * from UPR00400 where EMPLOYID = 'ELLIS0001'
I ran the latest UPDATE statement in this "case", just now. These are the results when running select * from on these two tables.
Thanks! Much appreciated!
TBSupport
UPR00100.rpt
UPR00400.rpt
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, JEaston! That worked!
TBSupport
TBSupport
If so, try adding an ELSE to the case which will trigger for anything else.
For example:
UPDATE UPR00100 SET Primary_Pay_Record = CASE WHEN UPR00400.PAYRCORD = 'SALY' AND UPR00400.EMPLOYID = 'ELLI0001' THEN 1 WHEN UPR00400.PAYRCORD <> 'SALY' AND UPR00400.EMPLOYID <> 'ELLI0001' THEN 0 ELSE 0 END FROM UPR00100 INNER JOIN UPR00400 ON UPR00100.EMPLOYID = UPR00400.EMPLOYID