Hi Patrik,

You're only grouping by ID so you can only include that column in the select list without one of the aggregate functions (sum, min, max, etc.).

Tim.

________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Patrik Schindler <poc@xxxxxxxxxx>
Sent: 22 January 2020 16:27
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: 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://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.pocnet.net%2Fpoc-key.asc&amp;data=02%7C01%7C%7Cb07ccddebaa344e280bf08d79f4fae38%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153036883194745&amp;sdata=LHxOdlG3N7VQW6dD4z%2Bi8ZO58oXjaZ0iAbolAlOUhPE%3D&amp;reserved=0


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7Cb07ccddebaa344e280bf08d79f4fae38%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153036883204756&amp;sdata=%2B8CP3DZzfvcjXwT7isDeSxcz2DX8G8Xnbsqe8kmlmfI%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7Cb07ccddebaa344e280bf08d79f4fae38%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153036883204756&amp;sdata=UhBcujVqp%2BUZeHEwk8k4YufbVU%2F8mu9L1wAIjMPHk2s%3D&amp;reserved=0.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://nam01.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7Cb07ccddebaa344e280bf08d79f4fae38%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153036883204756&amp;sdata=dsPO8PEnXRsjUSoPEczrEc%2BMSNGBIV88YPDWDev2hpo%3D&amp;reserved=0

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.