Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Null Values question for SQL update

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
kplkpl4

New User


Joined: 14 Sep 2006
Posts: 4
Location: Tampa

PostPosted: Thu Sep 14, 2006 12:16 am    Post subject: Null Values question for SQL update
Reply with quote

UPDATE S.SPOMRAS T1
SET (I_DLR_CHRG) =
(SELECT VALUE(T2.I_CURR, T1.I_DLR_CHRG)
FROM S.S53DRFT T2
WHERE T2.C_DLR_PART_SUPPLY = '61888000'
AND T1.I_MRA = T2.I_MRA
AND T1.D_UPD = '2006-02-24'
AND T2.I_CURR IS NOT NULL)

DSNT408I SQLCODE = -407, ERROR: AN UPDATE, INSERT,
OR SET VALUE IS NULL, BUT THE OBJECT COLUMN CANNOT
CONTAIN NULL VALUES

The send field does not have null values. How does the system view this to be null?
Back to top
View user's profile Send private message

DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Thu Sep 14, 2006 4:24 am    Post subject: Re: Null Values question for SQL update
Reply with quote

kplkpl4

The problem you are experiencing is that the SELECT for the SET is getting a not found (SLQCODE = 100). I know whet you wanted to do, If you cannot find row from S.S53DRFT use the existing value from S.SPOMRAS. Problem is that the SELECT returns a NULL, not I_CURR is null.

When I code UPDATES, I find it helpful to code the set of rows I will be updating first, before I code the SET. First think about this code. This is that set of rows you want to update in S.SPOMRAS, right?

Code:

UPDATE S.SPOMRAS T1                                 
   SET (I_DLR_CHRG) = ?                             
                                         
 WHERE EXISTS                                       
       (SELECT *                                     
          FROM S.S53DRFT T2                         
         WHERE T2.C_DLR_PART_SUPPLY = '61888000'     
           AND T1.I_MRA = T2.I_MRA                   
           AND T1.D_UPD = '2006-02-24'               
           AND T2.I_CURR IS NOT NULL                 
         )                                           


Then code the SET, You know you have a value for I_CURR at this point. Now, if you want to update with a default value if no value is found for i_CURR, there is another technique that can be used. But it could cause much more overhead updating all rows.

Code:

UPDATE S.SPOMRAS T1                                 
   SET (I_DLR_CHRG) =                               
       (SELECT T2.I_CURR                             
          FROM S.S53DRFT T2                         
         WHERE T2.C_DLR_PART_SUPPLY = '61888000'     
           AND T1.I_MRA = T2.I_MRA                   
           AND T1.D_UPD = '2006-02-24'               
           AND T2.I_CURR IS NOT NULL                 
         )                                           
 WHERE EXISTS                                       
       (SELECT *                                     
          FROM S.S53DRFT T2                         
         WHERE T2.C_DLR_PART_SUPPLY = '61888000'     
           AND T1.I_MRA = T2.I_MRA                   
           AND T1.D_UPD = '2006-02-24'               
           AND T2.I_CURR IS NOT NULL                 
         )                                           


Let me know if this does not solve the problem

Dave
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Sorting of hex values Saurabh_mi DFSORT/ICETOOL 10 Thu May 25, 2017 3:49 pm
No new posts Fileaid Update Replace with null string descann Compuware & Other Tools 4 Mon May 15, 2017 3:00 pm
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts QUESTION: RETURN CHANNEL pahiker CA Products 21 Thu Apr 20, 2017 12:31 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us