Re: Retrieved record on a chain - file allows duplicate keys

Something like this should do it. The CTE invoices, gets a list of all
invoice numbers that have the pair invoicenum, item more than once. The
result set from invoice can have an invoice more than once since item A and
item B can each be on invoice 123 more than once creating 2 records. Thus
the need for the count distinct over those invoices.

with invoices as (
select invoicenum
from your_table
group by invoicenum, item
having count(*) > 1
)

select count(distinct invoicenum)
from invoices

On Wed, May 12, 2021 at 11:42 AM Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>
wrote:

Another curiosity question in this situation.

If I wanted a count of the number of invoices that have an item appearing
more than once, how would I do that in SQL?

I simply don't know enough to know what to google. Though I'm thinking the
count function will be in there twice . . .





On Wed, May 12, 2021 at 11:42 AM Patrik Schindler <poc@xxxxxxxxxx> wrote:

Hello Jeff,

Am 12.05.2021 um 16:30 schrieb Jeff Crosby <jlcrosby@xxxxxxxxxxxxxxxx>:

If I do a Chain to this file and there are multiple matching records,

will

I _always_ get the same record? Like (maybe) the one with the lowest

RRN?

Or might it be different from 1 Chain to the next?

As far as I've observed, you'll get always the same record, as long as

the

physical (RRN) order within the given (second) key field value isn't
changed. This can happen with REUSEDLT(*YES) plus frequent

deletes/writes,

or even a simple RGZPFM to reclaim unused space.

A while ago, I've coded a small load-paged subfile application (viewing
call data records from my Asterisk PBX, being fed into the PF via ODBC)
with a view-detail screen, relying solely on RRNs. I've created the PF

with

SQL, so I can't do a CHGPFM REUSEDLT(*NO). But since I won't delete

records

there, this is not an issue.

:wq! PoC

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--


*Jeff Crosby*
VP Information Systems
UniPro FoodService/Dilgard
P.O. Box 13369
Ft. Wayne, IN 46868-3369
260-422-7531
direct.dilgardfoods.com

The opinions expressed are my own and not necessarily the opinion of my
company. Unless I say so.
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com