I am trying to write an UPDATE statement for Table 1, based on a SUM of a column in Table 2. I have it working in MS SQL Server, against SQL tables that are copies of iSeries files, but when I copy-paste to Navigator and run against iSeries files, I get an error.
This is my iSeries/Navigator code. Did I make a silly typo ?
ERROR:
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token . was not valid. Valid tokens: <IDENTIFIER>. Cause . . . . . : A syntax error was detected at token .. Token . is not a valid token. A partial list of valid tokens is <IDENTIFIER>. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token .. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
This is my iSeries/Navigator code. Did I make a silly typo ?
Code:
UPDATE MYLIB.INVCPX00 SET WHOCPL = (select SUM(Z.WHOCPL) FROM MYLIB.INVCPZ00 Z WHERE Z.WHDATE = X.WHDATE and Z.WHTIME = X.WHTIME and Z.WHFLYR = X.WHFLYR and Z.WHFLQR = X.WHFLQR and Z.WHFLPD = X.WHFLPD and Z.WHFPDY = X.WHFPDY and Z.WHWHSE = X.WHWHSE and Z.WHSEQ = X.WHSEQ and Z.WHLTY = X.WHLTY AND Z.WHSEQ IN ('1', '2') AND Z.WHZONE IN ('BLD', 'FGW', 'RMW', 'FGA', 'RMA' ) GROUP BY Z.WHDATE, Z.WHTIME, Z.WHFLYR, Z.WHFLQR, Z.WHFLPD, Z.WHFPDY, Z.WHWHSE, Z.WHSEQ, Z.WHLTY ) FROM MYLIB.INVCPX00 X JOIN MYLIB.INVCPZ00 Z on Z.WHDATE = X.WHDATE and Z.WHTIME = X.WHTIME and Z.WHFLYR = X.WHFLYR and Z.WHFLQR = X.WHFLQR and Z.WHFLPD = X.WHFLPD and Z.WHFPDY = X.WHFPDY and Z.WHWHSE = X.WHWHSE and Z.WHSEQ = X.WHSEQ and Z.WHLTY = X.WHLTY WHERE X.WHSEQ IN ('1', '2') ;
ERROR:
SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token . was not valid. Valid tokens: <IDENTIFIER>. Cause . . . . . : A syntax error was detected at token .. Token . is not a valid token. A partial list of valid tokens is <IDENTIFIER>. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token .. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.
Comment