Re: Checking Thresholds in a file using SQL

Clarifying with an example.

K1 is Member Id
F1 is First Name
F2 is Last Name
F3 is Address

etc.

I know First name Last name would not change normally for the same Member
Id, but Address could be different, so treat this just as an example.

If the file has more than 500 Member Ids (the defined threshold) with same
Address, or more than 300 Members with same Last Name or more than 1000
Members with same first name, I want to abort.

If I can get the highest count for each field then I can do the checking
for the thresholds separately.

Vinay

On Tue, Oct 20, 2015 at 2:07 PM, Alan Shore <ashore@xxxxxxxx> wrote:

Hi Vinay
Question
Is F1 one of the 10 fields and K1 is the other 9
OR
Is F1 nine of the 10 fields and K1 is the other 1
OR
Some other 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

-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of
Vinay Gavankar
Sent: Tuesday, October 20, 2015 1:59 PM
To: Midrange Systems Technical Discussion
Subject: Checking Thresholds in a file using SQL

Hi,

I have a requirement where I need to check for number times a value occurs
in a field in the file.

The file has a field K1 (non-unique) and another field F1

For the records with same value of K1, F1 can have different values. I
need to consider only the value of last record (highest RRN).
If my file has 3 records for K1=A (in RRN sequence)
A XXX
A YYY
A ZZZ

then I want to consider only ZZZ for processing.

I need to get a count of unique, non-blank values of F1 in the file
(considering only the last record for K1).

To give an example, if my full file looks like this:

A XXX
A YYY
*A ZZZ*
*B XXX*
C MMM
*C ZZZ*
*D XXX*

*E UUU*

*F ZZZ*
G (blanks)
H (blanks)

then my counts should be:
ZZZ 3
XXX 2
UUU 1

If the highest count is greater than a certain number (threshold), I have
to take a particular action.

So basically, I just need the highest count. The value of F1 for which
highest count occurred is not important.

Is this possible to do with sql (embedded in RPGLE).

Or what would be the most efficient (I/O and CPU wise) way of doing this?

The file actually has 10 fields (F1 thru f10) and the above needs to be
done for all the 10 fields (could be a different threshold value for all
fields).

Any help will be greatly appreciated.

Thanks
Vinay
--
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.