This is cool Rob! Thanks for sharing!
Steve Needles
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Monday, September 09, 2013 12:31 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL question
Now, just to take this a little further, you can get subtotals by date, and a grand total. And, you can do that with only one select statement.
....+....1....+....2....+....3....+....4....+....5....+
Object Change COUNT OBJECT DATE_GROUP
Date
DELETEME2 010209 1 0 0
NODDSPF 010209 1 0 0
- 010209 2 1 0
AAAKEYL1 010509 1 0 0
ROB2 010509 1 0 0
- 010509 2 1 0
QHTML 011209 1 0 0
- 011209 1 1 0
CHAMARA1 011309 1 0 0
CHAMARA2 011309 1 0 0
- 011309 2 1 0
...
...
...
- - 556 1 1
SELECT odobnm, odldat, count(odobnm) as count,
Grouping(odobnm) as object,
grouping(odldat) as date_group
FROM routines/dskdtl
where odlbnm='ROB' and odobtp='*FILE'
group by cube(odobnm, odldat)
order by odldat, odobnm
Study CUBE and ROLLUP.
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: fbocch2595@xxxxxxx
To: midrange-l@xxxxxxxxxxxx,
Date: 09/09/2013 12:34 PM
Subject: Re: SQL question
Sent by: midrange-l-bounces@xxxxxxxxxxxx
Thanks Buck, that worked good;
Display Data
Data width . . . . . . : 58
Position to line . . . . . Shift to column . . . . .
.
....+....1....+....2....+....3....+....4....+....5....+...
Object Change Change TOTAL
Date Time
W88PLCHP 90,813 92,249 0
W88PLTRP 90,813 92,250 0
W88PTYPP 90,813 92,251 0
W88REIMP 90,813 92,252 0
W88SMPCP 90,813 92,253 0
W88SMPLP 90,813 92,253 0
W88SMPPP 90,813 92,254 0
W88SMPTP 90,813 92,255 0
W88SPDLP 90,813 92,256 0
W88WORKP 90,813 92,256 0
W88WORKT 90,813 92,257 0
W89BHSTP 90,813 92,258 0
W89PCNPP 90,813 92,259 0
W90RSKCP 90,813 92,300 0
W92TXTBP 90,813 92,300 0
W99PTYPP 90,813 92,301 0
TOTAL 9,999,999 0 1,671
******** End of data ********
SELECT odobnm, odldat, odltim, 0 as total FROM myfile
where odldat = 090713 or odldat = 090813
nion
elect 'TOTAL', 9999999, 0, count(*) as total from myfile
here odldat = 090713 or odldat = 090813
rder by 2
-----Original Message-----
From: Buck Calabro <kc2hiz@xxxxxxxxx>
To: midrange-l <midrange-l@xxxxxxxxxxxx>
Sent: Mon, Sep 9, 2013 12:19 pm
Subject: Re: SQL question
On 9/9/2013 11:47 AM, fbocch2595@xxxxxxx wrote:
Hi Buck, that statement gave me;
Sorry Frank, paste problem on my end :-(
SELECT odobnm, odldat, odltim, 0 as total FROM myfile
where odldat = 090713 or odldat = 090813
nion
elect 'TOTAL', 9999999, 0, count(*) as total from myfile
here odldat = 090713 or odldat = 090813
rder by 2
--buck
--
his is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
o post a message email: MIDRANGE-L@xxxxxxxxxxxx
o subscribe, unsubscribe, or change list options,
isit:
http://lists.midrange.com/mailman/listinfo/midrange-l
r email: MIDRANGE-L-request@xxxxxxxxxxxx
efore posting, please take a moment to review the archives
t
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 communication, including attachments, is confidential, may be subject to legal privileges, and is intended for the sole use of the addressee. Any use, duplication, disclosure or dissemination of this communication, other than by the addressee, is prohibited. If you have received this communication in error, please notify the sender immediately and delete or destroy this communication and all copies.
TRVDiscDefault::1201
midrange.com
