Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    16

    Unanswered: 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. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    282
    Provided Answers: 41
    What's your Db2 version?

    The following works on my 7.3:

    Code:
    DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPLOYEE (
    PRURN2 INT, PRURN1 INT, PRURN5 INT, EMP# INT
    ) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
    
    DECLARE GLOBAL TEMPORARY TABLE SESSION.HOURBANK (
    POINTS INT, EAEMP# INT
    ) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
    
    DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPATTEN (
    eatapt INT, EAEMP# INT, EAABDA VARCHAR(10), REHIDT VARCHAR(10)
    ) WITH REPLACE ON COMMIT PRESERVE ROWS NOT LOGGED;
    
    UPDATE SESSION.EMPLOYEE SET PRURN2 = PRURN1 - (PRURN5 + 
    (SELECT POINTS FROM SESSION.HOURBANK WHERE EMP# = EAEMP#) +
    Case 
      When (
        Select SUM(eatapt) As eatapt From SESSION.EMPATTEN Where Left(TRIM(eaabda),4) = Left(TRIM(rehidt),4) And eaabda < rehidt And EMP# = EAEMP#
      ) Is Null 
      Then 0
      Else (
        Select SUM(eatapt) As eatapt From SESSION.EMPATTEN Where Left(TRIM(eaabda),4) = Left(TRIM(rehidt),4) And eaabda < rehidt And EMP# = EAEMP#
      ) 
    End
    )
    WHERE EXISTS (SELECT 1 FROM SESSION.HOURBANK WHERE EMP# = EAEMP#);
    BTW, you can simplify your query with:
    Code:
    UPDATE SESSION.EMPLOYEE SET PRURN2 = PRURN1 - (PRURN5 + 
    (SELECT POINTS FROM SESSION.HOURBANK WHERE EMP# = EAEMP#) +
    coalesce((
    Select SUM(eatapt) As eatapt From SESSION.EMPATTEN Where Left(TRIM(eaabda),4) = Left(TRIM(rehidt),4) And eaabda < rehidt And EMP# = EAEMP#
    ), 0)
    )
    WHERE EXISTS (SELECT 1 FROM SESSION.HOURBANK WHERE EMP# = EAEMP#);
    Regards,
    Mark.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •