Re: Use of MAX?

Thanks for giving us the message id. BIG help.
"SQL0122 - Column OHPTTC or expression in SELECT list not valid."
WRKMSGD MSGID(SQL0122) MSGF(QSQLMSG)
5=Display details
1. Display message text
...
-- Column name &1 is specified in the SELECT clause but not in the GROUP
BY clause.
...

Now, let's take your sample
orde rep CODE DATE TIME
123 BOB AAA 072215 090000
123 BOB AAA 072215 093600

Let me doctor it just a wee bit.
orde rep CODE DATE TIME
123 BOB AAA 072315 090000
123 BOB AAA 072215 093600
The reason I doctored it is to show you the problem of using max(date) and
max(time). In this example you can see that the max date is on one row
and the max time is on another. You may need to concatenate them. Let's
say you just want the first row, with the greatest date, and if there are
two times with the same date then check time also, for the same order,
rep, code.
So if I have this data
OTORD# OTUSRN OTTRNC OTTRND OTTRT
123 BOB AAA 20,150,723 90,000
123 BOB AAA 20,150,722 93,600

select otord#, otusrn, ottrnc,
max(ottrnd*1000000 + ottrt) as datetime
from qtemp.temp
group by otord#, otusrn, ottrnc
order by otord#, otusrn, ottrnc

Ends up with
OTORD# OTUSRN OTTRNC DATETIME
123 BOB AAA 20,150,723,090,000

Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1
Group Dekko
Dept 1600
Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





From: Hoteltravelfundotcom <hoteltravelfun@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/22/2015 08:47 AM
Subject: Re: Use of MAX?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>



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 Wed, Jul 22, 2015 at 8:36 AM, Jim Oberholtzer <
midrangel@xxxxxxxxxxxxxxxxx> wrote:

Carel,

I understand your point but that's not the goal of a list like this.

Many

times we answer the same question, asked a different way than the last
time.
All folks benefit, and most folks on this list simply read the posts and
rarely if ever post themselves, even folks that have not run across this
SQL
function yet will be helped. I ask SQL questions and yes I could take

a

class, read a book etc., but my knowledge is good enough for my needs

most

of the time, so I was helped by this answer even though I did not pose

it.

If you don't like a question or list member, ignore them but never

berate

them for asking a question.


--
Jim Oberholtzer
Chief Technical Architect
Agile Technology Architects


-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Carel
Teijgeler
Sent: Wednesday, July 22, 2015 4:56 AM
To: Midrange Systems Technical Discussion
Subject: Re: Use of MAX?

Without sounding offending, but this is a simple RTFM.

This has been advised many times to you, but for every simple SQL

question

you come to this list, while a simple search on the Internet or the
appropriate manual would have helped you.

Have you ever considered to take a course in SQL or read a book/article

on

the basic principles on SQL? I advice you to do it. It saves you a lot

of

time.

Regards,
Carel Teijgeler

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.

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