Getting a Token Error When Running on iSeries

[StevenDyke]

The following sql statement runs from an external IDE but gives me an error on line 15 position 14 When ran in the iSeries environment:
Error: Position 14 Token SUM was not valid. Valid tokens: ) ,.

0011.00 UPDATE OIPAYFILES/EMPLOYEE SET PRURN2 = PRURN1 - (PRURN5 + (SELECT
0012.00 POINTS FROM WEBPRDDT1/HOURBANK WHERE EMP# = EAEMP#) +
0013.00 Case When (Select SUM(eatapt) As eatapt From OIPAYFILES/EMPATTEN
0014.00 Where Left(TRIM(eaabda),4) = Left(TRIM(rehidt),4) And eaabda < rehidt And EMP# = EAEMP#) Is Null Then 0
0015.00 Else (Select SUM(eatapt) As eatapt From OIPAYFILES/EMPATTEN
0016.00 Where Left(TRIM(eaabda),4) = Left(TRIM(rehidt),4) And eaabda < rehidt And EMP# = EAEMP#) End)
0017.00 WHERE EXISTS (SELECT 1 FROM WEBPRDDT1/HOURBANK WHERE EMP# = EAEMP#)

[Jim_IT]

Steven,

The error message you are receiving is because the use of other tables is not allowed in an update statement.

This can be accomplished using the merge statement.

merge into OIPAYFILES/EMPLOYEE as x
using
(select EAEMP#,POINTS+coalesce(sum(EATAPT),0) as TOTAL_POINTS
 from WEBPRDDT1/HOURBANK
 left join OIPAYFILES/EMPATTEN
 on left(trim(EAABDA),4) = left(trim(REHIDT),4) 
 and EAABDA < REHIDT
 group by EAEMP#,POINTS) as y
on (x.EMP#=y.EAEMP#)
when matched then update set x.PRURN2=x.PRURN1-x.PRURN5+y.TOTAL_POINTS

Jim