Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Cannot Insert Null Value into...

Posted on 2013-05-30
10
Medium Priority
?
418 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 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 72

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

Technology Partners: 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!

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

577 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