RE: Checking Thresholds in a file using SQL

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.