Re: SQL question

Not exactly, I want the output to show the 3 fields but total the # of odobnm's. Thanks to all, for giving it a shot.
Thanks, Frank


-----Original Message-----
From: rob <rob@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Sent: Mon, Sep 9, 2013 11:04 am
Subject: Re: SQL question


You mean like this?
With T1 as (
ELECT odldat, count(odobnm) as NbrObjects
ROM myfile
here odldat = 090713 or odldat = 090813
roup by odldat
rder by odldat )
ELECT myfile.odobnm, myfile.odldat, myfile.odltim, T1.NbrObjects
ROM myfile Join T1 on myfile.odldat=T1.odldat
rder by odldat
Would look rather goofy though:
ODOBNM ODLDAT ODLTIM NBROBJECTS
YGOODPRGM 090713 125537 2
YOTHRPRGM 090713 062233 2
HISOBJECT 090813 041522 3
HATOBJECT 090813 193217 3
THROBJECT 090813 220515 3

ob Berendt
-
BM Certified System Administrator - IBM i 6.1
roup Dekko
ept 1600
ail to: 2505 Dekko Drive
Garrett, IN 46738
hip to: Dock 108
6928N 400E
Kendallville, IN 46755
ttp://www.dekko.com


From: fbocch2595@xxxxxxx
o: midrange-l@xxxxxxxxxxxx,
ate: 09/09/2013 10:38 AM
ubject: Re: SQL question
ent by: midrange-l-bounces@xxxxxxxxxxxx


i Bob, yea that works and that's something I could run...but I wish that
ur SQL would report on all fields but total/count odobnm. Also, thanks
ob for this;
SELECT odldat, count(odobnm) as NbrObjects
ROM myfile
here odldat = 090713 or odldat = 090813
roup by odldat
rder by odldat

-----Original Message-----
rom: Bob Levad <blevad@xxxxxxxxxxxxxxxx>
o: 'midrange-l@xxxxxxxxxxxx' <midrange-l@xxxxxxxxxxxx>
ent: Mon, Sep 9, 2013 10:17 am
ubject: RE: SQL question

ry this:
ELECT COUNT(odobnm)
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
----Original Message-----
om: midrange-l-bounces@xxxxxxxxxxxx [
ailto:midrange-l-bounces@xxxxxxxxxxxx]
Behalf Of fbocch2595@xxxxxxx
nt: Monday, September 09, 2013 9:01 AM
: midrange-l@xxxxxxxxxxxx
bject: Re: SQL question
i Mark, that doesn't work, says not valid;
olumn ODOBNM or expression in SELECT list not valid.
ELECT COUNT(odobnm), odobnm, odldat, odltim
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
ORDER BY odldat

ELECT COUNTodobnm), odobnm, odldat, odltim
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
ORDER BY odldat

----Original Message-----
om: Mark S Waterbury <mark.s.waterbury@xxxxxxxxxxxxx>
: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
nt: Mon, Sep 9, 2013 9:44 am
bject: Re: SQL question
ELECT COUNTodobnm), odobnm, odldat, odltim
FROM myfile
WHERE odldat = 090713 OR odldat = 090813
ORDER BY odldat
On 9/9/2013 9:23 AM, fbocch2595@xxxxxxx wrote:
i Folks, when I use the following statement I get a list of files but now

t to know the count or total of the odobnm's in the query...what's the
yntax
that or how can I get that number within by adding to the select
tatement?
hanks, Frank
SELECT odobnm, odldat, odltim FROM myfile
here odldat = 090713 or odldat = 090813
rder by odldat
-
s is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
post a message email: MIDRANGE-L@xxxxxxxxxxxx
subscribe, unsubscribe, or change list options,
it: http://lists.midrange.com/mailman/listinfo/midrange-l
email: MIDRANGE-L-request@xxxxxxxxxxxx
ore posting, please take a moment to review the archives
http://archive.midrange.com/midrange-l.
-
is is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
post a message email: MIDRANGE-L@xxxxxxxxxxxx
subscribe, unsubscribe, or change list options,
sit: http://lists.midrange.com/mailman/listinfo/midrange-l
email: MIDRANGE-L-request@xxxxxxxxxxxx
fore posting, please take a moment to review the archives
http://archive.midrange.com/midrange-l.
his electronic transmission and any documents accompanying this
lectronic
ansmission contain confidential information belonging to the sender. This
nformation may be legally privileged. The information is intended only for
he
e of the individual or entity named above. If you are not the intended
cipient, you are hereby notified that any disclosure, copying,
istribution,
the taking of any action in reliance on or regarding the contents of
his
ectronically transmitted information is strictly prohibited.

is is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
post a message email: MIDRANGE-L@xxxxxxxxxxxx
subscribe, unsubscribe, or change list options,
sit: http://lists.midrange.com/mailman/listinfo/midrange-l
email: MIDRANGE-L-request@xxxxxxxxxxxx
fore posting, please take a moment to review the archives
http://archive.midrange.com/midrange-l.