Solved

Cannot Insert Null Value into...

Posted on 2013-05-30
10
400 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 40

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
 
LVL 68

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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 40

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Error in query 3 52
SQL Query 3 49
AWE-based memory on 32-bit servers 1 23
check the deletion of SQL job on who delete/disable it 12 29
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

896 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now