Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Cannot Insert Null Value into...

Posted on 2013-05-30
10
Medium Priority
?
413 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 11

Expert Comment

by:John Easton
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 71

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 11

Expert Comment

by:John Easton
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 11

Accepted Solution

by:
John Easton earned 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

715 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