ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Getting a Token Error When Running on iSeries

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Getting a Token Error When Running on iSeries

    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#)

  • #2
    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.

    Code:
    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

    Comment

    Working...
    X