SQL syntax error with fields and expressions in SELECT

Hello,

maybe someone can shed a light on this?

Given are two PFs, which get filled with data from a script via ODBC. For easier handling I renamed the recfmt name to the name of the PF (which makes the DDS compiler complain, so normally I don't do that).

A R RESPF
A VMNAME 64A
A HOSTNAME 64A
A GUESTNAME 64A
A LASTCHECK Z
A IPADRESS 16A
A STGCOMMITT 8S 3
A STGUNCOMM 8S 3
A STGRUNSHRD 8S 3
A STGOSUSED 8S 3
A ID R
A MEMSIZE 6S 0
A CPUNUM 3S 0
A K VMNAME

A R RESKONPF
A POOLNAME 50A
A STGINKL 8S 0
A STGMAX 8S 0
A CPUINKL 6S 0
A CPUMAX 6S 0
A MEMINKL 6S 0
A MEMMAX 6S 0
A BANDWIDTH 6S 0
A ID R
A IPADDRESS 3S 0
A IPSUBNET 3S 0
A K ID

I want to create a report for all IDs in respf which exceed certain limits in reskonpf. I had this one running on Linux with MySQL successfully (and the subtle different MySQL'ish SQL syntax). Trying to port the SQL to IBM i 7.2, I got stuck:

SELECT
id,
SUM(memSize)/1024 AS Mem_Used_GB,
memMax AS Mem_Max_GB,
SUM(stgOSUsed) AS Storage_Used_GB,
stgMax AS Storage_Max_GB
FROM vmresmgmt/respf
LEFT JOIN vmresmgmt/ResKonPF USING (id)
GROUP BY id
HAVING (
NOT (IFNULL(ResKonPF.memMax, 0) = 0 OR IFNULL(ResKonPF.stgMax, 0) = 0 )
) AND (
memSize > ResKonPF.memMax OR stgOSUsed > ResKonPF.stgMax
)
ORDER BY id

Yields: "Spalte MEMMAX oder Ausdruck in SELECT-Liste nicht gültig." aka "Invalid column MEMMAX or expression in SELECT-list invalid."

I could boil the issue at hand down to apparently SUM(column) and columns without SUM can't be used together with DB2/UDB. Is there another way to have that report showing calculated values on the fly?

Thanks!

:wq! PoC

PGP-Key: DDD3 4ABF 6413 38DE - https://www.pocnet.net/poc-key.asc