SQL Error in RPG

[MFisher]

Similar to my SQL related post, I am struggling with syntax or some other misunderstanding.

EDITED POST:
Got some help from someone at work. I think I was over complicating it.
This code seems to work.

C/EXEC SQL
C+ UPDATE INVCPX00 X
C+ set WHOCPL = (select SUM(Z.WHOCPL)
C+ FROM INVCPZ00 Z
C+ WHERE Z.WHDATE = X.WHDATE and Z.WHTIME = X.WHTIME
C+ and Z.WHFLYR = X.WHFLYR and Z.WHFLQR = X.WHFLQR
C+ and Z.WHFLPD = X.WHFLPD and Z.WHFPDY = X.WHFPDY
C+ and Z.WHWHSE = X.WHWHSE and Z.WHSEQ = X.WHSEQ
C+ and Z.WHLTY = X.WHLTY
C+ AND Z.WHZONE IN ('BLD', 'FGW', 'RMW', 'FGA', 'RMA' ))
C+ where x.whseq in ('1', '2')
C/END-EXEC

END EDIT

I am trying to run an UPDATE statement.
When I run it in MS SQL Server, it works fine.

When I copy-Paste into RPG SQL, I get an error.

0287.02 C/EXEC SQL                                                            
0287.03 C+ UPDATE INVCPX00                                                    
0287.04 C+ SET  WHOCPL = (select SUM(Z.WHOCPL)                                
0287.05 C+     FROM   INVCPZ00 Z                                              
0287.06 C+     WHERE Z.WHDATE = X.WHDATE and Z.WHTIME = X.WHTIME              
0287.07 C+       and  Z.WHFLYR = X.WHFLYR and Z.WHFLQR = X.WHFLQR            
0287.08 C+       and Z.WHFLPD = X.WHFLPD and  Z.WHFPDY = X.WHFPDY            
0287.09 C+       and  Z.WHWHSE = X.WHWHSE and  Z.WHSEQ = X.WHSEQ              
0287.10 C+       and  Z.WHLTY = X.WHLTY AND    Z.WHSEQ IN ('1', '2')          
0287.11 C+       AND Z.WHZONE IN ('BLD',  'FGW', 'RMW', 'FGA', 'RMA' )        
0287.12 C+     GROUP BY Z.WHDATE, Z.WHTIME, Z.WHFLYR, Z.WHFLQR,              
0287.13 C+       Z.WHFLPD, Z.WHFPDY, Z.WHWHSE,  Z.WHSEQ, Z.WHLTY )            
0287.14 C+ FROM INVCPX00 X                                                    
0287.15 C+ JOIN   INVCPZ00 Z on Z.WHDATE = X.WHDATE                          
0287.16 C+   and Z.WHTIME = X.WHTIME and  Z.WHFLYR = X.WHFLYR                
0287.17 C+   and Z.WHFLQR = X.WHFLQR and  Z.WHFLPD = X.WHFLPD                
0287.18 C+   and  Z.WHFPDY = X.WHFPDY and  Z.WHWHSE = X.WHWHSE                
0287.19 C+   and  Z.WHSEQ = X.WHSEQ and  Z.WHLTY = X.WHLTY                    
0287.20 C+ WHERE    X.WHSEQ IN ('1', '2')                                    
0287.22 C/END-EXEC

ERROR:

Message ID . . . . . . : SQL0199


Message . . . . : Keyword FROM not expected. Valid tokens: USE SKIP WAIT
WITH FETCH LIMIT ORDER WHERE OFFSET.

Cause . . . . . : The keyword FROM was not expected here. A syntax error
was detected at keyword FROM. The partial list of valid tokens is USE SKIP
WAIT WITH FETCH LIMIT ORDER WHERE OFFSET. This list assumes that the
statement is correct up to the unexpected keyword. The error may be earlier
in the statement but the syntax of the statement seems to be valid up to
this point.

[Rocky]

You have the FROM INVCP200 Z at the right place - but you also have FROM INVCPX00 X

It's the latter it's whining about - what are you trying to accomplish by having that second FROM?

[MFisher]

I am calculating a SUM on a column in file INVCP200 ,
and using that to update a field in INVCPX00.

In MS SQL Server, I need the "FROM INVCPX00 X"

on line 287.14 so I can do a

JOIN FROM INVCPX00 X on the next line to get a 1-for-1 record match.

[MFisher]

I edited post #1 with an update.

I am not familiar enough with RPG SQL and differences with MS SQL.

[Rocky]

If I'm understanding what you're trying to accomplish - try:

UPDATE INVCPX00 X
  SET WHOCPL =
      (
        SELECT SUM(Z.WHOCPL)
          FROM 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)
  WHERE EXISTS (
      SELECT *
        FROM INVCPZ00 Y
        WHERE Y.WHDATE = X.WHDATE AND
              Y.WHTIME = Y.WHTIME AND
              Y.WHFLYR = X.WHFLYR AND
              Y.WHFLQR = X.WHFLQR AND
              Y.WHFLPD = X.WHFLPD AND
              Y.WHFPDY = X.WHFPDY AND
              Y.WHWHSE = X.WHWHSE AND
              Y.WHSEQ = X.WHSEQ AND
              Y.WHLTY = X.WHLTY) AND
  X.WHSEQ IN ('1', '2')