Re: SQL syntax error with fields and expressions in SELECT

Hi Patrik,

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? 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.

Either way, you need to somehow decide which of the potential MAX_MEM values for the same ID should be included in the result, if they're guaranteed to all the same just use min() or max().

Tim.





________________________________
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Patrik Schindler <poc@xxxxxxxxxx>
Sent: 22 January 2020 16:53
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 16:34 schrieb Tim Fathers <X700-IX2J@xxxxxxxxxxx>:

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.).

Since this works with MySQL, this is a restriction from DB2/UDB, I guess. So I need to do that report "by Hand", with SQL *and* glue code?

:wq! PoC

PGP-Key: DDD3 4ABF 6413 38DE - https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.pocnet.net%2Fpoc-key.asc&amp;data=02%7C01%7C%7Ca372b8c1e28b4f72339208d79f533ba6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153052158354540&amp;sdata=veSHHaVYm8JJynrAiFrDflkC%2BQQuBX42kCS5sLkd0Hg%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://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fmidrange-l&amp;data=02%7C01%7C%7Ca372b8c1e28b4f72339208d79f533ba6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153052158354540&amp;sdata=YH2ayTg0Siy4CRbLinFN6cztiWPtsaHX7A1V67LSh5I%3D&amp;reserved=0
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fmidrange-l&amp;data=02%7C01%7C%7Ca372b8c1e28b4f72339208d79f533ba6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153052158364442&amp;sdata=0TiM373a1j3Pue%2BrmRESJczVyvLcW5gOVVn0ohkpNq4%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://nam10.safelinks.protection.outlook.com/?url=https%3A%2F%2Famazon.midrange.com&amp;data=02%7C01%7C%7Ca372b8c1e28b4f72339208d79f533ba6%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637153052158364442&amp;sdata=NXYYeZRS8axNZ8gFeOAgI5Jd5ZmK3GN2onk9nj0yDIs%3D&amp;reserved=0