|
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,Many
I understand your point but that's not the goal of a list like this.
times we answer the same question, asked a different way than the lasta
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
class, read a book etc., but my knowledge is good enough for my needsmost
of the time, so I was helped by this answer even though I did not poseit.
berate
If you don't like a question or list member, ignore them but never
them for asking a question.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
you come to this list, while a simple search on the Internet or theon
appropriate manual would have helped you.
Have you ever considered to take a course in SQL or read a book/article
the basic principles on SQL? I advice you to do it. It saves you a lotof
time.list
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
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,list
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
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 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.