Re: MIDRANGE-L Digest, Vol 14, Issue 1489

Hi Vinay,

With the exception of F2 through F10, this does what you want, or gets you
close to it.

Processing by RRN is risky. If the file gets reorganized, or if "reuse
deleted rows" is turned on for the table, the rows may not stay in their
original RRN order.

Anytime you involve RRN processing using the RRN() function, performance
may not be optimum. About how many rows are in this table?

with
CTE_ROW_NUM_ASSIGNED as (
select K1, F1, row_number() over( partition by K1
order by rrn( MY_FILE ) desc
) as ROW_NUM
from MY_FILE
where F1 <> ''
)

select F1, count(*) as MY_COUNT
from CTE_ROW_NUM_ASSIGNED
where ROW_NUM = 1
group by F1
;

Mike

date: Tue, 20 Oct 2015 13:59:19 -0400
from: Vinay Gavankar <vinaygav@xxxxxxxxx>
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