Re: SQL syntax error with fields and expressions in SELECT

Patrik,

Having had a better look, I think you could refactor your SQL as follows using a common table expression, which avoids the problems with the aggregated columns and the duplicate expressions. Hopefully it works as I haven't tested it!

with STORAGE_SUMMARY as (
select id,
sum(memSize) / 1024 AS Mem_Used_GB,
sum(stgOSUsed) AS Storage_Used_GB

from VMRESMGMT.RESPF

order by id
)

select *,
memMax AS Mem_Max_GB,
stgMax AS Storage_Max_GB

from STORAGE_SUMMARY

left join VMRESMGMT.RESKONPF using (id)

where ifnull(ResKonPF.memMax, 0) > 0 and ifnull(ResKonPF.stgMax, 0) > 0 and (
Mem_Used_GB > ResKonPF.memMax OR Storage_Used_GB > ResKonPF.stgMax
)

Tim.


________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Patrik Schindler <poc@xxxxxxxxxx>
Sent: 22 January 2020 17:46
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL syntax error with fields and expressions in SELECT

Hello Tim,

Am 22.01.2020 um 17:19 schrieb Tim Fathers <X700-IX2J@xxxxxxxxxxx>:

I thought you must be mistaken, but surprisingly, when I tried it in MariaDB/MySQL it works! However, it seems to pick arbitrary values which is not what you ought to rely on in any case so I think DB2 failing is the correct thing to do.

:-)

The problem is this, if you have a table as follows:

ID MAX_MEM
1 1
1 5
2 2
2 5
2 8
3 7

...and you do

select ID, MAX_MEM from TABLE group by ID

How should SQL know which value from MAX_MEM should be included in the summarised row?

Good point. I'm not sure if your example applies, though, because there's just one row in ResKonPF per ID. Maybe that's the reason why MySQL was doing the right thing besides the SQL being ambiguous.

As I said above, it seems MySQL is indeterminate, which is never a good thing, so you need to either select min(MAX_MEM), max(MAX_MEM) or use some other aggregating function to aggregate/select the value. If there's some other column that dictates which MAX_MEM value has priority then you can use the OLAP functions to order and pick the value you need.

Okay, understood.

I got this one to run properly and show the same values as in MySQL:

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, memMax, stgMax
HAVING (
NOT (IFNULL(ResKonPF.memMax, 0) = 0 OR IFNULL(ResKonPF.stgMax, 0) = 0 )
) AND (
(SUM(memSize)/1024) > ResKonPF.memMax OR (SUM(stgOSUsed)) > ResKonPF.stgMax
)
ORDER BY id

Important to note is that I can't use alias names as defined in "SELECT foo AS bar" within the HAVING clause. I must repeat the exact expression within HAVING as used in the SELECT. Also something where MySQL behaves different (and calculates the expression just once while DB2 may optimise the same calculations to one shot or not).

Thanks for your Help, Tim! Greatly appreciated!

:wq! PoC

PGP-Key: DDD3 4ABF 6413 38DE - https://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.pocnet.net%2Fpoc-key.asc&amp;data=02%7C01%7C%7Cfa33ee4987cc4581ce5608d79f5a9c7f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153083825436466&amp;sdata=qakgsDElxmGkgnP%2Falkf%2Fl3fInibj0HV0ocQSaucXIo%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://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7Cfa33ee4987cc4581ce5608d79f5a9c7f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153083825436466&amp;sdata=PMLagsC0ngXvvaTQdCmJOzajaokXPRUt08mDKLJG9uE%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7Cfa33ee4987cc4581ce5608d79f5a9c7f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153083825436466&amp;sdata=fqLYtpbE7HNooxbq5MHYvJDy70y%2BFLmZqhS6CqWdtTc%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://eur04.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7Cfa33ee4987cc4581ce5608d79f5a9c7f%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153083825436466&amp;sdata=Z0ztiJqg85bxhD7XOJd8vfE%2Buh8h%2BDja0DS%2Bj%2BNcz3o%3D&amp;reserved=0