kplkpl4
New User
Joined: 14 Sep 2006 Posts: 4 Location: Tampa
|
|
|
|
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? |
|
DavidatK
Active Member
Joined: 22 Nov 2005 Posts: 700 Location: Troy, Michigan USA
|
|
|
|
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 |
|