Re: SEQUEL Help needed

[Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>]

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