Thanks for the reply Michael
As it happens - I finally worked out what I wanted
I sent a subsequent e-mail saying that I finally realized all of the mistakes that I had made
However - seeing your example gives me more confidence in what I eventually ended up with
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
-----Original Message-----
From: Mayer, Michael [mailto:MMayer@xxxxxxxxxxxxxx]
Sent: Thursday, September 24, 2020 12:38 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Cc: Alan Shore <ashore@xxxxxxxx>
Subject: [EXTERNAL] Re: Need help on capturing data in a query
Alan, here's how I get row number's in several of my ACS SQL's .... from 1 of my BRMS SQL's:
This will get a unique row # for each record. Not sure that's what you want but it's worth having anyway.
Select Row_Number() over (Order by BKSSVT) as "Row #", SUBSTR(BKHSVT, 2, 6) AS "Bkup Date", BKVOL AS "Tape #", BKHSYS AS "System",
BKHLIB AS "Lib", BKHSEQ AS "Seq#", BKHSAV AS "Obj Sv", BKHERR AS "Obj N Sv", BKHSTA AS "Sts 0=N 1=S",
BKHSIZ AS "Blk Sz Bytes", BKHMSG AS "Error Msg ID", BKTYPS AS "Save Type", BKHGRP AS "Ctl Grp", BKHCMD AS "B/Up Cmd",
BKSAVT AS "Tape Y/N", SUBSTR(BKSSVT, 8, 6) AS "Start Time", SUBSTR(BKESVT, 8, 6) AS "End Time", BKHTIM AS "Bkup Time",
BKHTXT AS "Text"
FROM QUSRBRM.QA1AHS
GROUP BY BKHSVT, BKHPRC, BKHTIM, BKVOL, BKHSYS, BKHLIB, BKHSEQ, BKHSAV, BKHERR, BKHSTA,
BKHMSG, BKHSIZ, BKTYPS, BKHGRP, BKHCMD, BKSAVT, BKHPRC, BKSSVT, BKESVT, BKHTXT
Result looks something like this:
Row # Bkup DateTape # Lib Seq# Obj Sv Obj N Sv
1 200924 01090B PDPDDB 372 119 0
2 200924 01090B YAFRDTA 373 4 0
3 200924 01090B TFBDB 374 28 0
4 200924 01090B DC@DTALIB 375 4 0
Very Respectfully,
Michael Mayer
IBM i Support / System Admin.
IT Operations.
The Florida Bar
651 E. Jefferson St
Tallahassee, Florida 32399-2300
mmayer@xxxxxxxxxxxxxx
https://www.floridabar.org
Office: 850.561.5761
Cell: 518.641.8906
message: 2
date: Thu, 24 Sep 2020 14:34:38 +0000
from: Alan Shore via MIDRANGE-L <midrange-l@xxxxxxxxxxxxxxxxxx>
subject: RE: [EXTERNAL] RE: Need help on capturing data in a query
Hi Birgitta
Sorry to bother you about this - but I am getting myself well and truly lost on this.
This is a snapshot of the data.
CBCUST CBINV# CBCHK# DATE_PAID CBPAID CBDISC Row_number
22130 000036 ADJ113384030 12/10/2019 834.37 17.03 1
22130 000037 WT-112713849 12/2/2019 5,238.96 106.92 1
22130 000038 WT-112713849 12/2/2019 4,896.65 99.93 1
22130 000038 ADJ116572038 1/9/2020 15.92 0.32 2
22130 000038 ADJ ACCT 1/16/2020 85.26 0.00 3
22130 000039 WT-112561765 11/26/2019 483.10 9.86 1
22130 000047 WT-112680341 11/26/2019 138,010.14 2,816.54 1
22130 000047 WT-114103881 12/17/2019 82.50 1.68 2
22130 000047 WT-115715006 12/27/2019 501.33 10.23 3
22130 000047 ADJ116592825 1/10/2020 116.24 2.37 4
22130 000047 ADJ ACCT 7/17/2020 435.41 0.00 5
22130 000156 WT-112561765 11/26/2019 10,746.17 219.31 1
The columns CBCUST, CBINV#, CBCHK#, DATE_PAID, CBPAID, CBDISC are from my file INVXTRACT I am trying to generate the Row_Number for the CBCUST,CBINV combination
Alan Shore
E-mail : ASHORE@xxxxxxxx
Phone [O] : (631) 200-5019
Phone [C] : (631) 880-8640
'If you're going through hell, keep going.'
Winston Churchill
________________________________
Please note: Florida has very broad public records laws. Many written communications to or from The Florida Bar regarding Bar business may be considered public records, which must be made available to anyone upon request. Your e-mail communications may therefore be subject to public disclosure.
midrange.com
