Link to home
Start Free TrialLog in
Avatar of TBSupport
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
Avatar of John Easton
John Easton
Flag of United Kingdom of Great Britain and Northern Ireland image

Do you have any records where Payrcord='Sally' and EmployID <> 'ELLI001' or vice versa.  If so they are not included in your Case statement.

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
Avatar of TBSupport
TBSupport

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
Avatar of Sharath S
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 

Open in new window

What about if you only update rows which meet the condition? Or do you need to forcefully change it on every row?
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
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:

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 

Open in new window


If this doesn't work, as requested above please post some data from your table(s).
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
ASKER CERTIFIED SOLUTION
Avatar of John Easton
John Easton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you, JEaston!  That worked!

TBSupport