Thanks Chuck, I really appreciate all this work you put in here!
I will give this a try.

Glenn

A review of the data [by SELECT * FROM QTEMP/COUNTS] produced by the
following script may assist to better grasp possible data reduction in
the join. The script only provides information about the first file. A
similar script can be performed on the other files, creating a specific
/counts/ file and choosing each of the fields in the join and selection
[and optionally those in the format]:

create table qtemp/counts
(rqs char(15)
,Total int
,cde int
,co int
,br int
,dv int
,fly int
,fle int
,fsx int
,TAG int
,SQ# int
,DLQ int
,asn int
,ckd int
)
;

insert into qtemp/counts
select cast('No Selection ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
;

insert into qtemp/counts
select cast('tag=/b ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
where tk@tag = ''
;

insert into qtemp/counts
select cast('tag+sq#¬0 ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
where tk@tag = ''
and tk@sq# <> 0
;

insert into qtemp/counts
select cast('tag+sq#+dlq¬0 ' as char(15)) as rqs
,count(*) as Total
,count(distinct tk@cde) as cde
,count(distinct tk@co ) as co
,count(distinct tk@br ) as br
,count(distinct tk@dv ) as dv
,count(distinct tk@fly) as fly
,count(distinct tk@fle) as fle
,count(distinct tk@fsx) as fsx
,count(distinct TK@TAG) as TAG
,count(distinct TK@SQ#) as SQ#
,count(distinct TK@DLQ) as DLQ
,count(distinct TK@ASN) as asn
,count(distinct TK@CKD) as ckd
from tk@mst
where tk@tag = ''
and tk@sq# <> 0
and tk@dlq <> 0
;

Using grouped counts across the keys, is more informative to see the
actual values being joined; i.e. correlating a count to a specific [key]
value.

Regards, Chuck

This thread ...

Replies:

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

This mailing list archive is Copyright 1997-2019 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].