× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.




Hmm.  For each F1, you need the following:

1. Total number of records
2. Total number of distinct F2
3. Total number of distinct F3/F4

The number of records to be written will be (2 * 3) - 1.  This is close to what Paul was saying.

Check the math on these.  For your examples.  Pretty sure it works.

with T1 as (
  select F1,
  count(*) TF1,
  count(distinct F2) TF2,
  count(distinct F3||F4) TF3F4
  from MYFILE group by F1)
select sum((TF2 * TF3F4) - TF1) from T1

On 7/17/2018 5:05 PM, Vinay Gavankar wrote:
I was looking for some help to create an SEQUEL to find out how many
records would get added when I run a program.

I am not sure how to best formulate my problem, but let me try:

I have one large file (180 million recs) with fields F1, F2, F3, F4

It is defined as Unique on fields F2, F3, F4

I will be writing a program to create additional records in file if for a
particular value of F1, there exist more than one value of F2 and each
combination of F1/F2 does not have same F3/F4 combination.

I want to know how many additional records will be created by the program.

I think it is best explained by examples of data in the records in file:

Assume field F1 has values An, F2 has Bn, F3 has Cn and F4 has Dn

Example1:
A1, B1, C1, D1
No new records will be created for A1

Example2:
A2, B2, C2, D2
A2, B2, C3, D3
A2, B3, C2, D2
A2, B3, C3, D3
No new records will be created for A2

Example 3:
A3, B4, C4, D4
A3, B4, C5, D5
A3, B5, C6, D6
Following new records will be created for A3
A3, B4, C6, D6
A3, B5, C4, D4
A3, B5, C5, D5

Example 4:
A4, B6, C7, D7
A4, B6, C8, D8
A4, B7, C7, D7
Following new records will be created for A4
A4, B7, C8, D8

I hope I haven't made a typo and made myself clear.

Even though it MAY be possible to insert these records thru SEQUEL, it
won;t be allowed in production environment, so I will have to write a
program.

I am just trying to figure up-front out how many records will be added by
the program.

Thanks in advance.

Vinay



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.