Re: SQL syntax error with fields and expressions in SELECT

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