Re: Use of MAX?

To expand on what Birgitta said - just adding MAX on a column will cause this error. This is because MAX() is an aggregate or grouping function. In other words, it applies to a group of records.

To get the group, you have to use a GROUP BY, and you have done this. But your GROUP BY is grouping the wrong columns - you have grouped by the columns you added MAX() to - it will make sense that you can't get the MAX of the values you are grouping on.

So your GROUP BY has to be a list of all the OTHER columns - the ones that do not have MAX on them.

There are some other ways to get a single record - I assume that in this case, the exact value of the 5 columns do not matter too much - so you can pick something like MAX or MIN or first record, stuff like that. If you are on at least v5r4, there are some OLAP functions, like ROW_NUMBER and related stuff that could be used. But in every case, your use of MAX is one of the easiest.

So take those 5 columns out of the GROUP BY and put the others in that list - the ones from T01.OHPTTC to T02.RFDTA

Good luck
Vern

On 7/22/2015 7:46 AM, Hoteltravelfundotcom wrote:

Briggitta, I have the same sql as previous all I am doing is adding the MAX
to the 5 columns. I want to avoid displaying what is a duplicate row. When
the rep entered the same order#, code, etc we only want to show 1. SO I
though MAX would give me only one in this case. Or do you use DISTINCT?
you have

orde rep CODE DATE TIME
123 BOB AAA 072215 090000
123 BOB AAA 072215 093600

I only want one of these and the date time will never be the same the other
3 cols if the same i just need 1 row.

On 22-7-2015 0:15, Hoteltravelfundotcom wrote:

HI I want to use MAX in this SQL. Because we have some cases where
Duplicates are entered. IN this particular sales data, there is no
check in the application.

So I have a reporting tool using a view and would need to remove these
dupes. The dupes will be manifest in these 5 fields and the time is
the only one that will differ (possibly the date) so If I select on
MAX I would get a distinct: So far it is not allowing, it gives an
error:

"SQL0122 - Column OHPTTC or expression in SELECT list not valid."
The dupes appear here: an example: We want only the 001 row but only
1 of the next 2
---------------------------------
OTORD# OTUSRN OTTRNC OTTRND OTTRT

05648230 MMOLINA 001 20,150,610 105,428
05648230 MMOLINA RRF 20,150,610 113,127
05648230 MMOLINA RRF 20,150,610 105,443



SELECT max(T01.OTORD#), max(T01.OTUSRN),max(T01.OTTRNC),
max(T01.OTTRND),
max(T01.OTTRT), T01.OHPTTC, T01.OHSLR#, T01.OHORDT,
T01.OHORDD, T01.OHTTN$, T02.RFCAT, T02.RFSLC, T02.RFSQ2, T02.RFDTA
FROM SLSDTA.CLSPAYT22 T01 INNER JOIN REPDTA.REFERRF T02 ON
T01.OTTRNC = T02.RFSLC WHERE RFCAT = '5058' AND RFSQ2 = '1'

GROUP BY T01.OTORD#, T01.OTUSRN, T01.OTTRNC, T01.OTTRND, T01.OTTRT

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.