× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.




As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2024 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.