Solved

Cannot Insert Null Value into...

Posted on 2013-05-30
10
407 Views
Last Modified: 2013-05-31
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
0
Comment
Question by:TBSupport
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 10

Expert Comment

by:JEaston
ID: 39208308
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
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39209184
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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39209225
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

0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 69

Expert Comment

by:Qlemo
ID: 39209544
What about if you only update rows which meet the condition? Or do you need to forcefully change it on every row?
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39209575
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
0
 
LVL 41

Expert Comment

by:Sharath
ID: 39209584
Can you post some sample data from your table which is not working
0
 
LVL 10

Expert Comment

by:JEaston
ID: 39210065
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).
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39210398
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
0
 
LVL 10

Accepted Solution

by:
JEaston earned 500 total points
ID: 39210479
I think I see what is happening here.  The join is bringing up 7 records.  The first one would return 1, but the the next 6 return 0 so the end product is you end up with 0.

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 
       LEFT JOIN (SELECT * FROM UPR00400 WHERE PAYRCORD = 'SALY') AS UPR00400
               ON UPR00100.EMPLOYID = UPR00400.EMPLOYID 

Open in new window


Therefore if there is a record of 'SALY' it will show a 1, if there isn't a SALY record it will show 0.
0
 
LVL 1

Author Comment

by:TBSupport
ID: 39212112
Thank you, JEaston!  That worked!

TBSupport
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question