When using any aggregate function in a SELECT statement, you need to specify
all columns without an aggregate function within the GROUP BY clause.
You only listed the columns from the first table.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Hoteltravelfundotcom
Gesendet: Wednesday, 22.7 2015 00:15
An: Midrange Systems Technical Discussion
Betreff: Use of MAX?
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.
midrange.com
