IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Null Values question for SQL update


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts INCLUDE OMIT COND for Multiple values... DFSORT/ICETOOL 5
No new posts Replace Multiple Field values to Othe... DFSORT/ICETOOL 12
No new posts Null values are considered in Total c... DFSORT/ICETOOL 6
No new posts Question for file manager IBM Tools 7
No new posts question for Pedro TSO/ISPF 2
Search our Forums:

Back to Top