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



oh ok then distinct on the first four should do the trickl

On Wed, Jul 22, 2015 at 9:34 AM, <rob@xxxxxxxxx> wrote:

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.



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.