Transcript JOIN, MATCH and MORE - Information Builders

JOIN, MATCH and MORE–
Making the Connections
Walter F. Blood
Technical Director, FOCUS Division
May, 2011
Copyright 2007, Information Builders. Slide 1
JOIN, MATCH and MORE
Basic Choices
When your report requires data from
many different sources…
Employee
Data
Job Data
Support Data
Department
Data
Customer
Data
Purchase
Orders
Product
Data
Invoices
Inventory Data
…you need JOIN, MATCH or MORE to
make the connections!
Copyright 2007, Information Builders. Slide 2
JOIN, MATCH and MORE
Basic Choices
Each connction involves many choices:
? Equal fields or Conditions
? Real Fields or Virtual Fields
? One-to-One or One-to-Many
? Inclusive or exclusive
? FOCUS Managed or Interface Managed
? Same or different file types
Copyright 2007, Information Builders. Slide 3
JOIN, MATCH and MORE
JOIN – the Nested Loop
Nested Loop
A
B
C
Copyright 2007, Information Builders. Slide 4
JOIN, MATCH and MORE
JOIN - Basic Characteristics
JOIN treats multiple structures as one
Employee
Data
Connects
Department
Data
Product
Data

data sources based on fields or conditions
 Creates a vertical concatenation
 Excludes only comma, tab and token delimited filesInvoices
Job
Data
Maximum 1022 active
JOINS
Customer
Data
 JOINed structure maximum
1024 segments
Inventory Data
 JOINed record maximum 32K
Support Data
Purchase
Orders
Copyright 2007, Information Builders. Slide 5
JOIN , MATCH and MORE
Equal JOIN Syntax
Departm
entData
Employee
Data
Customer
Data
Job Data
LONG FORM
Support Data
JOIN
field1 [ AND field2 …] [TAG tagname]
IN file1 TO [ALL] fielda [AND fieldb…] IN file2
[TAG tagname] AS joinname
END
Purchase
Orders
Product
Data
Invoices
Inventory Data
SHORT FORM
JOIN field1 IN file1 TO [ALL] field2 IN file2 AS name
JOIN EMPID IN EMPLOYEE TO EMPID IN DEPARTMENT AS JEMP
Copyright 2007, Information Builders. Slide 6
JOIN, MATCH and MORE
Equal JOIN Syntax - DEFINE
Employee
Data
Job Data
LONG FORM
JOIN
field1 WITH fieldname [TAG tagname]
IN file1 TO [ALL] fielda IN file2
[TAG tagname] AS joinname
END
Support Data
Departm
entData
Product
Data
Customer
Data
Purchase
Orders
Invoices
Inventory Data
SHORT FORM
JOIN field1 WITH field IN file1 TO [ALL] field2 IN file2
JOIN INVID WITH PRDID IN PRODUCT TO INVID IN INVOICES AS INV
DEFINE FILE PRODUCT
INVID/A25=EDIT(&YMD) | PRDID;
…
Copyright 2007, Information Builders. Slide 7
JOIN, MATCH and MORE
JOINS – Direct INNER/LEFT OUTER Join
Equal JOIN
JOIN {LEFT_OUTER/INNER} field {AND field …}
IN hostfile {TAG tagname} TO {UNIQUE/MULTIPLE}
crfield {AND crfield … } IN crfile {TAG tagname}
AS joinname
END
Equal JOIN – DEFINE based
JOIN {LEFT_OUTER/INNER} deffield WITH field
IN hostfile {TAG tagname} TO {UNIQUE/MULTIPLE}
crfield IN crfile {TAG tagname} AS joinname
END
Copyright 2007, Information Builders. Slide 8
JOIN, MATCH and MORE
Equal JOIN Rules
All rules are determined by the SUFFIX of the TO file
TO sqltable
Multiple target fields may be specified
Indexes are not required, but preferred
Many-to-many supported
TO FOCUS/XFOCUS file
Only single target field may be specified
Target field must be indexed
Many-to-many NOT supported
Copyright 2007, Information Builders. Slide 9
JOIN – Making the Connection
Equal JOIN Rules
All rules are determined by the SUFFIX of the TO file
TO Indexed Files
Target field -- primary key/alternate index
Multiple target fields may be specified
High-order elements of key or alternate index
Many-to-many supported
TO FIX/sequential files
Multiple target fields NOT supported
Many-to-many NOT supported
Both files must be sorted in ascending
order on the JOIN keys
Copyright 2007, Information Builders. Slide 10
JOIN, MATCH and MORE
Equal JOIN Syntax
 Multiple Fields - Field1 AND field2 …

Up to four fields may be specified
 TAG tagname
 Tagname becomes a prefix for fully qualifying fields in

specified file
joinname (default is blank)
 Identifies JOIN for the session
 Another JOIN with the same name will overlay
 Specified JOIN can be CLEARed
Copyright 2007, Information Builders. Slide 11
JOIN, MATCH and MORE
DEFINE-Based JOIN
JOIN fieldname WITH field IN file1 …
Multiple field JOIN not supported
WITH field must be a “real” field in the host file
DEFINE field ONLY on the host file – must be real field on
the cross-reference file
SET KEEPDEFINES = ON to use a dynamic DEFINE
specified before the JOIN
SET KEEPDEFINES = ON
DEFINE FILE CAR
CONT/A10 = …
END
JOIN CONT WITH COUNTRY IN CAR TO CONTINENT IN WORLD AS AJ
Copyright 2007, Information Builders. Slide 12
JOIN, MATCH and MORE
Conditional JOINs Syntax
Departm
entData
Employee
Data
Customer
Data
Job Data
JOIN FILE
from_file AT from_field [TAG from_tag ]
TO {ALL|ONE} FILE
to_file AT to_field [TAG to_tag]
[AS as_name]
[WHERE expression1 ;
WHERE expression2 ; ... ; ]
END
Purchase
Orders
Support Data
Product
Data
Invoices
Inventory Data
JOIN FILE EMPLOYEE AT EMPID
TO ALL FILE CAR AT SEATS AS J1
WHERE RETAIL_COST LE SALARY * 3;
END
Copyright 2007, Information Builders. Slide 13
JOIN, MATCH and MORE
JOINS –Direct INNER/LEFT OUTER Join
Conditional JOIN
JOIN {LEFT_OUTER/INNER} hostfile AT hfld1
{TAG tagname}
TO {UNIQUE/MULTIPLE} crfile AT crfld {TAG tagname}
AS joinname
{WHERE expression1;
{WHERE expression2:}
END
Copyright 2007, Information Builders. Slide 14
JOIN, MATCH and MORE
Conditional JOINs Rules and Caveats
The conditional JOIN is supported for
 FOCUS
 VSAM
 ADABAS
 IMS
 All relational data sources
Optimization of the conditional JOIN syntax depends on
 Specific data sources involved in the JOIN
 Complexity of the WHERE criteria
Where possible, use EQ-JOIN
 Index/Key always used
 No TABLE Scan
Copyright 2007, Information Builders. Slide 15
JOIN, MATCH and MORE
Conditional JOINs - Insurance Rates
Greater than
Minimum Age
JOIN FILE EMPDATA1 AT BIRTHDATE
TO ALL FILE RATES AT AGE AS J1
WHERE EMPDATA1.BAGE GE RATES.AGE;
WHERE EMPDATA1.BAGE LE RATES.EAGE;
END
TABLE FILE EMPDATA1
Less than
HEADING
Maximum Age
"To: <FIRSTNAME <LASTNAME "
"</1 Thank you for choosing our company for your <0X
insurance needs."
"Thank you for choosing our company for your insurance needs.”
"Since your birth date is <BIRTHDTATE ,your current rate is<0X
<RATE_PER_THOUSAND per"
"unit of coverage. This is your rate through age <EAGE . “
ON TABLE SET PAGE OFF
BY PIN NOPRINT PAGE-BREAK
END
Copyright 2007, Information Builders. Slide 16
JOIN, MATCH and MORE
Conditional JOINs - Insurance Rates and Letters
To: DANIEL VALINO
Thank you for choosing our company for your insurance needs.
Since your birth date is 07/20/1959, your current rate is $11 per
unit of coverage. This is your rate through age 42.
To: MICHAEL BELLA
Thank you for choosing our company for your insurance needs.
Since your birth date is 07/27/1952, your current rate is $24 per
unit of coverage. This is your rate through age 53.
Copyright 2007, Information Builders. Slide 17
JOIN, MATCH and MORE
FOCUS or Interface Managed?
Interface Managed
 Both HOST and Cross-Reference are same SUFFIX
 OPTIMIZATION is ON
 See Trace for optimization
FOCUS Managed
 Different file types
 OPTIMIZATION is turned OFF
 FOCUS turns off optimization (explained in TRACEs)
Copyright 2007, Information Builders. Slide 18
JOIN, MATCH and MORE
JOINS – One to Many Relationships
JOIN PIN IN EMPDATA TO
Employee
Data
Job Data
Departm
entData
Customer
Data
Product
Data
ALL PIN IN KIDS AS JOIN1
Invoices
END
Support Data
Purchase
Orders
Inventory Data
OUTER
EMPDATA
PIN
LASTNAME
FIRSTNAME
MIDINITIAL
INNER
KIDS
EMP_ID
LASTNAME
CHILDNAME
MIDINITIAL
Copyright 2007, Information Builders. Slide 19
JOIN, MATCH and MORE
JOINS – One to Many - Inner JOIN using SET ALL
SET ALL = OFF
PIN
--000000010
000000020
000000030
000000040
000000050
000000060
000000070
000000080
000000090
000000100
EMP_ID
-----000000010
000000010
000000010
000000010
000000030
000000040
000000060
000000070
Retrieves only matching pairs
Copyright 2007, Information Builders. Slide 20
JOIN, MATCH and MORE
JOINS – One to Many - Left-Outer with SET ALL
SET ALL = ON
PIN
--000000010
000000020
000000030
000000040
000000050
000000060
000000070
000000080
000000090
000000100
EMP_ID
-----000000010
000000010
000000010
000000010
000000030
000000040
000000060
000000070
Retrieves all records in Host
showing the short paths
Copyright 2007, Information Builders. Slide 21
JOINS – One to Many
Left Outer JOIN using SET ALL
PIN
--000000010
LASTNAME
-------VALINO
FIRSTNAME
--------DANIEL
000000020
000000030
000000040
000000050
000000060
000000070
000000080
000000090
000000100
BELLA
CASSANOVA
ADAMS
ADDAMS
PATEL
SANCHEZ
SO
PULASKI
ANDERSON
MICHAEL
LOIS
RUTH
PETER
DORINA
EVELYN
PAMELA
MARIANNE
TIM
CHILDNAME
--------ASTRIC
ARTHUR
ANNE
ANTHONY
.
JOHN
MARY
.
SAM
SAMANTHA
.
.
.
Short Paths
Copyright 2007, Information Builders. Slide 22
JOIN, MATCH and MORE
JOIN - Unique Relationship
JOIN PIN IN EMPDATA TO
Employee
Data
Job Data
Departm
entData
Customer
Data
Product
Data
PIN IN SPICE AS JOIN1
Invoices
END
Support Data
Purchase
Orders
Inventory Data
EMPDATA
SPICE
PIN
LASTNAME
FIRSTNAME
MIDINITIAL
PIN
LASTNAME
SPOUSENAME
SSN
Copyright 2007, Information Builders. Slide 23
JOIN, MATCH and MORE
Unique Relationship and SET ALL
SET ALL = OFF or SET ALL = ON
PIN
--000000010
000000020
000000030
000000040
000000050
000000060
000000070
000000080
000000090
000000100
PIN
EMP_ID
-------000000010
000000030
000000010
000000040
000000010
000000060
000000010
000000070
000000030
000000090
000000040
000000060
000000070
Copyright 2007, Information Builders. Slide 24
JOINs – One to One
Left Outer JOIN using SET ALL
PIN
LASTNAME
FIRSTNAME
---
--------
---------
000000010
VALINO
ARTHUR
000000020
BELLA
000000030
CASSANOVA
JOHN
000000040
ADAMS
MARY
000000050
ADDAMS
000000060
PATEL
SAM
000000070
SANCHEZ
SAMANTHA
000000080
SO
000000090
PULASKI
000000100
ANDERSON
Blanks
Copyright 2007, Information Builders. Slide 25
JOIN, MATCH and MORE
MATCH – Sort Merge
Sort Merge
B
A
C
Copyright 2007, Information Builders. Slide 26
JOIN, MATCH and MORE
MATCH - Basic Characteristics
MATCH merges files sequentially one at a time
Department
Data
Product
Data
Employee
Merges data sources based on common fields
Data
 Creates a vertical concatenation
 Connection based upon BY fields
Invoices
Data
Maximum 16 files connected in one MATCH request
Job
Customer
Data
 Produces only HOLD
output
 Output controlled with AFTER MATCH <mergephrase>
Inventory Data
 Offers
Right-Outer and Full-Outer capabilities
Support Data
Purchase
Orders
Copyright 2007, Information Builders. Slide 27
JOIN, MATCH and MORE
MATCH - Syntax
MATCH FILE file1
.
BY FLD
RUN
FILE file2
.
BY FLD1 AS FLD
[AFTER MATCH merge_phrase1]
RUN
FILE file3
.
BY FLD2 AS FLD
[AFTER MATCH merge_phrase2]
END
BY fields connect files
Merge phrase selects
Copyright 2007, Information Builders. Slide 28
JOIN, MATCH and MORE
MATCH – Mergetype and Output
mergetype
Specifies which of the retrieved records from the files are to be
selected
 OLD-OR-NEW – all records from first and second files.
This is the default if the AFTER MATCH line is omitted.
(The UNION of the sets.)
 OLD-AND-NEW – only records common to both files.
(The INTERSECTION of the sets.)
 OLD-NOT-NEW – records from the first file with no
match in the second file.
Copyright 2007, Information Builders. Slide 29
JOIN, MATCH and MORE
MATCH – Mergetype and Output
 NEW-NOT-OLD - records from second file with no match
in the first file.
 OLD-NOR-NEW - non-matching records from both files records from the first file with no match in the second file,
and records from the second file with no match in the first
file.
 OLD – records from the first file with matching records in
the second file.
 NEW – records from the second file with matching records
in the first file.
Copyright 2007, Information Builders. Slide 30
JOIN, MATCH and MORE
MATCH – Fine-Tuning
Fine tune control using the display verbs
SUM – summarizes data to one record
PRINT – prints each individual record
Four combinations
SUM / SUM  1st (summarized) 2nd (summarized)
SUM / PRINT  1st (summarized) 2nd (detail)
PRINT / SUM  1st (detail) 2nd (summarized)
PRINT / PRINT  1st (detail) 2nd (detail)
Copyright 2007, Information Builders. Slide 31
JOIN, MATCH and MORE
MATCH – Fine-Tuning 2
N Country DCost
1 England 1000
1 England 2000
2 Japan 3000
2 Japan 4000
MATCH FILE F1
<verb> COUNTRY DCOST
BY N
RUN
FILE F2
<VERB> CAR RCOST
BY N
AFTER MATCH HOLD OLD-OR-NEW
N Car RCost
1 Jaguar 5000
1 Jensen 6000
2 Datsun 4000
2 Toyota 7000
SUM  SUM
PRINT  SUM
1 England 3000 Jensen 11000
1 England 2000 Jensen 11000
2 Japan
2 Japan
7000 Toyota 11000
SUM  PRINT
4000 Toyota 11000
PRINT  PRINT
1 England 3000 Jaguar
5000
1 England 1000 Jaguar
5000
1 England 3000 Jensen
6000
1 England 2000 Jensen
6000
2 Japan
7000 Datsun
4000
2 Japan
3000 Datsun
4000
2 Japan
7000 Toyota
7000
2 Japan
4000 Toyota
7000
Copyright 2007, Information Builders. Slide 32
JOIN, MATCH and MORE
MORE - Basic Characteristics
MORE concatenates files sequentially one at a time
Employee
Data
Requires

Department
Data
Product
Data
data sources with common fields with the
same formats
Invoices
 Creates a horizontal concatenation
Job
Data
Concatenate records of different file types
Customer
Data
 Can be used with TABLE,
GRAPH, and MATCH
Inventory Data
 Similar to UNION functionality
Support Data
Purchase
Orders
Copyright 2007, Information Builders. Slide 33
JOIN, MATCH and MORE
MORE - Basic Characteristics
Retrieval of data from unlike data sources




Concatenate different file types:





FOCUS
DB2/SQL
IMS
VSAM
Fix files
Based on corresponding fields with:


Same name
Same FORMAT
Data appears as if from a single source
Currently supported in:
 TABLE
 GRAPH
 MATCH FILE
Copyright 2007, Information Builders. Slide 34
JOIN, MATCH and MORE
MORE Processing
Order of Processing
Main request
 Identifies data fields
 Identifies sorting criteria
 Identifies output format of data
Subrequests
 Identify file(s)
 Identify data fields to be concatenated to data in main
request
Copyright 2007, Information Builders. Slide 35
JOIN, MATCH and MORE
MORE Processing
Order of processing:
1
FOCUS
DATA
2
VSAM
DATA
SORT
OUTPUT
DATA
3
FIX
Copyright 2007, Information Builders. Slide 36
JOIN, MATCH and MORE
MORE Processing
FOCUS
VSAM
FIX
FIX
Data Retrieval
(DEFINE/IF)
Data Retrieval
(DEFINE/IF)
Data Retrieval
(DEFINE/IF)
MORE
EOF
N
Y
MORE
EOF
Y
N
EOF
Y
N
FOCSORT
COMPUTE/IF TOTAL
OUTPUT
Copyright 2007, Information Builders. Slide 37
JOIN, MATCH and MORE
MORE Processing
FOCUS
VSAM
FIX
File 1
 Main Request
 Supported TABLE, GRAPH, MATCH FILE
syntax e.g. BY WHERE, IF, COMPUTE
File 2
 Subrequest
 ONLY WHERE/IF syntax
File 3
 Subrequest
 ONLY WHERE/IF syntax
Copyright 2007, Information Builders. Slide 38
JOIN, MATCH and MORE
MORE Syntax
TABLE, GRAPH, MATCH FILE (Files)
BY, WHERE, etc.
MORE
FILE (File 2)
Subrequest (ONLY IF/WHERE)
MORE
FILE (File 3)
Subrequest (ONLY IF/WHERE)
END (after the last subrequest)
Copyright 2007, Information Builders. Slide 39
JOIN, MATCH and MORE
MORE Usage Notes



Based on fields in main request
Corresponding to fields In:
 Subrequest, based on
 Fieldname
 Format
All fields in main request must be available to:
 Files in the subrequest
 Real fields
 Defined fields
Copyright 2007, Information Builders. Slide 40
JOIN, MATCH and MORE
MORE Supported Datatypes
Alpha
 Type and length must be equal
INTEGER, floating point, DECIMAL
 Type must be the same
PACKED
 Type and scale must be equal
DATE (SMART)
 Always correspond
DATE (Legacy)
 Edit options must be the same
TEXT Fields are NOT supported
Copyright 2007, Information Builders. Slide 41
JOIN, MATCH and MORE
Which one?
Which One Do We Use and Why
Use
JOIN
EQ
JOIN
COND
Indexed
Field
Yes
No
N/A
N/A
Like field
Use
DEFINE
No
Yes
Use
DEFINE
No
common
fields
No
Yes
Yes
Use
DEFINE
With
MATCH
MORE
Copyright 2007, Information Builders. Slide 42
JOIN, MATCH and MORE
Making the Connections
Employee
Data
Job Data
Support Data
Department
Data
Customer
Data
Purchase
Orders
Product
Data
Invoices
Inventory Data
Copyright 2007, Information Builders. Slide 43
JOIN, MATCH and MORE
Making the Connections - Appendix
Copyright 2007, Information Builders. Slide 44
JOIN, MATCH and MORE
EMPDATA - Master
FILENAME=EMPDATA, SUFFIX=FOC
SEGNAME=EMPDATA,
SEGTYPE=S1
FIELDNAME=PIN,
ALIAS=ID,
FORMAT=A9,
INDEX=I,$
FIELDNAME=LASTNAME,
ALIAS=LN,
FORMAT=A15,
FIELDNAME=FIRSTNAME,
ALIAS=FN,
FORMAT=A10,
$
FIELDNAME=MIDINITIAL,
ALIAS=MI,
FORMAT=A1,
$
FIELDNAME=DIV,
ALIAS=CDIV,
FORMAT=A4,
$
FIELDNAME=DEPT,
ALIAS=CDEPT,
FORMAT=A20,
$
FIELDNAME=JOBCLASS,
ALIAS=CJCLAS,
FORMAT=A8,
$
FIELDNAME=TITLE,
ALIAS=CFUNC,
FORMAT=A20,
$
FIELDNAME=SALARY,
ALIAS=CSAL,
FORMAT=D12.2M,
$
FIELDNAME=HIREDATE,
ALIAS=HDAT,
FORMAT=YMD,
$
$
Copyright 2007, Information Builders. Slide 45
JOIN, MATCH and MORE
EMPDATA - Data
PIN
--000000010
000000020
000000030
000000040
000000050
000000060
000000070
000000080
000000090
000000100
LASTNAME
-------VALINO
BELLA
CASSANOVA
ADAMS
ADDAMS
PATEL
SANCHEZ
SO
PULASKI
ANDERSON
FIRSTNAME
--------DANIEL
MICHAEL
LOIS
RUTH
PETER
DORINA
EVELYN
PAMELA
MARIANNE
TIM
Copyright 2007, Information Builders. Slide 46
JOIN, MATCH and MORE
Kids - Master
FILENAME=KIDS
, SUFFIX=FOC
SEGNAME=CHILDSEG, SEGTYPE=S1
FIELDNAME=EMP_ID,
ALIAS=PIN,
FIELDNAME=LASTNAME,
ALIAS=SLN,
FORMAT=A15
,$
FIELDNAME=CHILDNAME,
ALIAS=SFN,
FORMAT=A10
,$
FIELDNAME=DATE_OF_BIRTH, ALIAS=DOB,
FORMAT=A9,
INDEX =I
,$
FORMAT=MDYY ,$
Copyright 2007, Information Builders. Slide 47
JOIN, MATCH and MORE
Kids - Data
EMP_ID
LASTNAME
CHILDNAME
DATE_OF_BIRTH
------
--------
---------
-------------
000000010
VALINO
ANTHONY
12/31/1980
000000010
VALINO
ANNE
11/09/1979
000000010
VALINO
ARTHUR
06/01/1982
000000010
VALINO
ASTRIC
05/03/1991
000000030
CASSANOVA
JOHN
05/07/1993
000000040
ADAMS
MARY
08/01/2000
000000060
PATEL
SAM
07/05/1998
000000070
SANCHEZ
SAMANTHA
08/04/1997
Copyright 2007, Information Builders. Slide 48
JOIN, MATCH and MORE
Spice - Master
FILENAME=SPICE
, SUFFIX=FOC
SEGNAME=SPOUSEI,
SEGTYPE=S1
FIELDNAME=PIN,
ALIAS=ID,
FORMAT=A9,
INDEX=I,$
FIELDNAME=LASTNAME,
ALIAS=SLN,
FORMAT=A15,$
FIELDNAME=SPOUSENAME, ALIAS=SFN,
FORMAT=A10,$
FIELDNAME=SPOUSESSN , ALIAS=SSN,
FORMAT=A9 ,$
Copyright 2007, Information Builders. Slide 49
JOIN, MATCH and MORE
Spice - Data
PIN
LASTNAME
SPOUSENAME
SPOUSESSN
---
--------
---------
----------
000000010
VALINO
ABIGAIL
000000011
000000030
CASSANOVA
EDWARD
000000032
000000040
ADAMS
BRIAN
000000043
000000060
PATEL
KEITH
000000064
000000070
SANCHEZ
EDWARD
000000075
000000090
PULASKI
DAVID
000000096
Copyright 2007, Information Builders. Slide 50
JOIN, MATCH and MORE
Insurance Rates File
TABLE FILE RATES PRINT *
AGE
EAGE
RATE_PER_THOUSAND
---
----
-----------------
20
26
$8
27
35
$9
36
42
$11
43
48
$14
49
53
$24
54
60
$30
61
65
$36
66
999
$42
Copyright 2007, Information Builders. Slide 51