copyright 2007, information builders. slide 1 join, match and more– making the connections walter...

Copyright 2007, Information Builders. Slide 1

JOIN, MATCH and MORE– Making the Connections

Walter F. Blood

Technical Director, FOCUS Division

May, 2011


JOIN, MATCH and MOREBasic Choices

EmployeeData

DepartmentData

Job Data

CustomerData Invoices

ProductData

Support Data PurchaseOrders

Inventory Data

When your report requires data from many different sources…

…you need JOIN, MATCH or MORE to make the connections!


JOIN, MATCH and MOREBasic 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

Each connction involves many choices:


JOIN, MATCH and MOREJOIN – the Nested Loop

Nested Loop

A B

C


JOIN, MATCH and MOREJOIN - Basic Characteristics

JOIN treats multiple structures as one

EmployeeData

DepartmentData

Job DataCustomerData

Invoices

ProductData

Support Data

PurchaseOrders

Inventory Data

Connects data sources based on fields or conditions

Creates a vertical concatenation

Excludes only comma, tab and token delimited files

Maximum 1022 active JOINS

JOINed structure maximum 1024 segments

JOINed record maximum 32K


JOIN , MATCH and MOREEqual JOIN Syntax

LONG FORMJOIN field1 [ AND field2 …] [TAG tagname] IN file1 TO [ALL] fielda [AND fieldb…] IN file2 [TAG tagname] AS joinnameEND

SHORT FORMJOIN field1 IN file1 TO [ALL] field2 IN file2 AS name

EmployeeData

DepartmentData

Job Data


ProductData


Inventory Data

JOIN EMPID IN EMPLOYEE TO EMPID IN DEPARTMENT AS JEMPJOIN EMPID IN EMPLOYEE TO EMPID IN DEPARTMENT AS JEMP


JOIN, MATCH and MOREEqual JOIN Syntax - DEFINE

LONG FORM

JOIN

field1 WITH fieldname [TAG tagname]

IN file1 TO [ALL] fielda IN file2

[TAG tagname] AS joinname

END

SHORT FORM

JOIN field1 WITH field IN file1 TO [ALL] field2 IN file2

EmployeeData

DepartmentData

Job Data


ProductData


Inventory Data

JOIN INVID WITH PRDID IN PRODUCT TO INVID IN INVOICES AS INV

DEFINE FILE PRODUCTINVID/A25=EDIT(&YMD) | PRDID;…

JOIN INVID WITH PRDID IN PRODUCT TO INVID IN INVOICES AS INV

DEFINE FILE PRODUCTINVID/A25=EDIT(&YMD) | PRDID;…


JOIN, MATCH and MORE JOINS – Direct INNER/LEFT OUTER Join

JOIN {LEFT_OUTER/INNER} field {AND field …} IN hostfile {TAG tagname} TO {UNIQUE/MULTIPLE} crfield {AND crfield … } IN crfile {TAG tagname} AS joinnameEND

JOIN {LEFT_OUTER/INNER} field {AND field …} IN hostfile {TAG tagname} TO {UNIQUE/MULTIPLE} crfield {AND crfield … } IN crfile {TAG tagname} AS joinnameEND

Equal JOIN

JOIN {LEFT_OUTER/INNER} deffield WITH field IN hostfile {TAG tagname} TO {UNIQUE/MULTIPLE} crfield IN crfile {TAG tagname} AS joinnameEND

JOIN {LEFT_OUTER/INNER} deffield WITH field IN hostfile {TAG tagname} TO {UNIQUE/MULTIPLE} crfield IN crfile {TAG tagname} AS joinnameEND

Equal JOIN – DEFINE based


JOIN, MATCH and MOREEqual JOIN Rules

TO sqltableMultiple target fields may be specifiedIndexes are not required, but preferredMany-to-many supported

TO sqltableMultiple target fields may be specifiedIndexes are not required, but preferredMany-to-many supported

TO FOCUS/XFOCUS fileOnly single target field may be specifiedTarget field must be indexedMany-to-many NOT supported

TO FOCUS/XFOCUS fileOnly single target field may be specifiedTarget field must be indexedMany-to-many NOT supported

All rules are determined by the SUFFIX of the TO file


JOIN – Making the ConnectionEqual JOIN Rules

TO Indexed Files Target field -- primary key/alternate index Multiple target fields may be specified High-order elements of key or alternate indexMany-to-many supported

TO Indexed Files Target field -- primary key/alternate index Multiple target fields may be specified High-order elements of key or alternate indexMany-to-many supported

TO FIX/sequential filesMultiple target fields NOT supportedMany-to-many NOT supported Both files must be sorted in ascending order on the JOIN keys

TO FIX/sequential filesMultiple target fields NOT supportedMany-to-many NOT supported Both files must be sorted in ascending order on the JOIN keys

All rules are determined by the SUFFIX of the TO file


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


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 fileDEFINE 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

SET KEEPDEFINES = ON

DEFINE FILE CAR

CONT/A10 = …

END

JOIN CONT WITH COUNTRY IN CAR TO CONTINENT IN WORLD AS AJ


JOIN, MATCH and MOREConditional JOINs Syntax

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

EmployeeData

DepartmentData

Job Data


ProductData


Inventory Data

JOIN FILE EMPLOYEE AT EMPID TO ALL FILE CAR AT SEATS AS J1 WHERE RETAIL_COST LE SALARY * 3;END

JOIN FILE EMPLOYEE AT EMPID TO ALL FILE CAR AT SEATS AS J1 WHERE RETAIL_COST LE SALARY * 3;END


JOIN, MATCH and MOREJOINS –Direct INNER/LEFT OUTER 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

JOIN {LEFT_OUTER/INNER} hostfile AT hfld1 {TAG tagname} TO {UNIQUE/MULTIPLE} crfile AT crfld {TAG tagname} AS joinname{WHERE expression1;{WHERE expression2:}END

Conditional JOIN


JOIN, MATCH and MOREConditional 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


JOIN, MATCH and MOREConditional JOINs - Insurance Rates

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 EMPDATA1HEADING"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-BREAKEND

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 EMPDATA1HEADING"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-BREAKEND

Greater than Minimum Age

Less than Maximum Age


JOIN, MATCH and MOREConditional 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: 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.

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.


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)


JOIN, MATCH and MOREJOINS – One to Many Relationships

JOIN PIN IN EMPDATA TO

ALL PIN IN KIDS AS JOIN1

END

PIN LASTNAME FIRSTNAME MIDINITIAL

EMPDATA

EMP_ID LASTNAME CHILDNAME MIDINITIAL

EMP_ID LASTNAME CHILDNAME MIDINITIAL

KIDSOUTER INNER

EmployeeData

DepartmentData

Job Data


ProductData


Inventory Data


JOIN, MATCH and MOREJOINS – One to Many - Inner JOIN using SET ALL

SET ALL = OFF

EMP_ID------000000010000000010000000010000000010000000030000000040 000000060 000000070

PIN --- 000000010 000000020 000000030 000000040 000000050 000000060 000000070 000000080 000000090 000000100 Retrieves only matching pairs


JOIN, MATCH and MOREJOINS – One to Many - Left-Outer with SET ALL

SET ALL = ON

EMP_ID------000000010000000010000000010000000010000000030000000040 000000060 000000070

PIN --- 000000010 000000020 000000030 000000040 000000050 000000060 000000070 000000080 000000090 000000100 Retrieves all records in Host

showing the short paths


JOINS – One to Many Left Outer JOIN using SET ALL

PIN LASTNAME FIRSTNAME CHILDNAME --- -------- --------- --------- 000000010 VALINO DANIEL ASTRIC ARTHUR ANNE ANTHONY 000000020 BELLA MICHAEL . 000000030 CASSANOVA LOIS JOHN 000000040 ADAMS RUTH MARY 000000050 ADDAMS PETER . 000000060 PATEL DORINA SAM 000000070 SANCHEZ EVELYN SAMANTHA 000000080 SO PAMELA . 000000090 PULASKI MARIANNE . 000000100 ANDERSON TIM .

PIN LASTNAME FIRSTNAME CHILDNAME --- -------- --------- --------- 000000010 VALINO DANIEL ASTRIC ARTHUR ANNE ANTHONY 000000020 BELLA MICHAEL . 000000030 CASSANOVA LOIS JOHN 000000040 ADAMS RUTH MARY 000000050 ADDAMS PETER . 000000060 PATEL DORINA SAM 000000070 SANCHEZ EVELYN SAMANTHA 000000080 SO PAMELA . 000000090 PULASKI MARIANNE . 000000100 ANDERSON TIM .

Short Paths


JOIN, MATCH and MORE JOIN - Unique Relationship

JOIN PIN IN EMPDATA TO

PIN IN SPICE AS JOIN1

END

PIN LASTNAME FIRSTNAME MIDINITIAL

EMPDATA

PIN LASTNAME SPOUSENAME SSN

SPICE

EmployeeData

DepartmentData

Job Data


ProductData


Inventory Data


JOIN, MATCH and MORE Unique Relationship and SET ALL

SET ALL = OFF or SET ALL = ON

PIN --- 000000010 000000030 000000040 000000060 000000070 000000090

PIN --- 000000010 000000020 000000030 000000040 000000050 000000060 000000070 000000080 000000090 000000100

EMP_ID------000000010000000010000000010000000010000000030000000040 000000060 000000070


JOINs – One to OneLeft 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

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


JOIN, MATCH and MOREMATCH – Sort Merge

Sort Merge

A B

C


JOIN, MATCH and MOREMATCH - Basic Characteristics

MATCH merges files sequentially one at a time

EmployeeData

DepartmentData


Invoices

ProductData

Support Data

PurchaseOrders

Inventory Data

Merges data sources based on common fields

Creates a vertical concatenation

Connection based upon BY fields

Maximum 16 files connected in one MATCH request

Produces only HOLD output

Output controlled with AFTER MATCH <mergephrase>

Offers Right-Outer and Full-Outer capabilities


MATCH FILE file1.BY FLDRUN FILE file2.BY FLD1 AS FLD[AFTER MATCH merge_phrase1]RUN FILE file3.BY FLD2 AS FLD[AFTER MATCH merge_phrase2]END

JOIN, MATCH and MOREMATCH - Syntax

BY fields connect files

Merge phrase selects


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.

mergetype

Specifies which of the retrieved records from the files are to be selected

JOIN, MATCH and MOREMATCH – 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.

JOIN, MATCH and MOREMATCH – Mergetype and Output


JOIN, MATCH and MOREMATCH – 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)


JOIN, MATCH and MOREMATCH – Fine-Tuning 2

N Country DCost1 England 10001 England 20002 Japan 30002 Japan 4000

N Car RCost1 Jaguar 50001 Jensen 60002 Datsun 40002 Toyota 7000

SUM SUM

1 England 3000 Jensen 11000

2 Japan 7000 Toyota 11000

PRINT SUM



SUM PRINT

1 England 3000 Jaguar 5000


2 Japan 7000 Datsun 4000


PRINT PRINT

1 England 1000 Jaguar 5000


2 Japan 3000 Datsun 4000


MATCH FILE F1<verb> COUNTRY DCOSTBY NRUN FILE F2<VERB> CAR RCOSTBY NAFTER MATCH HOLD OLD-OR-NEW


JOIN, MATCH and MOREMORE - Basic Characteristics

MORE concatenates files sequentially one at a time

EmployeeData

DepartmentData


Invoices

ProductData

Support Data

PurchaseOrders

Inventory Data

Requires data sources with common fields with the

same formats

Creates a horizontal concatenation

Concatenate records of different file types

Can be used with TABLE, GRAPH, and MATCH

Similar to UNION functionality


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

Retrieval of data from unlike data sources

JOIN, MATCH and MOREMORE - Basic Characteristics


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

JOIN, MATCH and MOREMORE Processing


Order of processing:

OUTPUTSORT

1DATA

FOCUS

2 DATAVSAM

3DATA

FIX



FIX

Data Retrieval(DEFINE/IF)

FOCUS

MORE

VSAM


MORE

FIX


EOF Y N

EOF Y N

FOCSORT

EOF Y N

COMPUTE/IF TOTAL

OUTPUT



File 1 Main Request

Supported TABLE, GRAPH, MATCH FILE syntax e.g. BY WHERE, IF, COMPUTE

File 3 Subrequest

ONLY WHERE/IF syntaxFIX

File 2 Subrequest

ONLY WHERE/IF syntax

VSAM

FOCUS



TABLE, GRAPH, MATCH FILE (Files)

BY, WHERE, etc.

MOREFILE (File 2)

Subrequest (ONLY IF/WHERE)MORE

FILE (File 3)Subrequest (ONLY IF/WHERE)END (after the last subrequest)

JOIN, MATCH and MOREMORE Syntax


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

JOIN, MATCH and MOREMORE Usage Notes


AlphaType and length must be equal

INTEGER, floating point, DECIMALType must be the same

PACKEDType and scale must be equal

DATE (SMART)Always correspond

DATE (Legacy)Edit options must be the same

TEXT Fields are NOT supported

JOIN, MATCH and MOREMORE Supported Datatypes


Which One Do We Use and Why

JOIN, MATCH and MOREWhich one?

Use

With

JOIN

EQ

JOIN

COND

MATCH MORE

Indexed

Field

Yes No N/A N/A

Like field Use

DEFINE

No Yes Use

DEFINE

No common

fields

No Yes Yes Use

DEFINE


JOIN, MATCH and MORE Making the Connections

EmployeeData

DepartmentData

Job Data


ProductData


Inventory Data


JOIN, MATCH and MORE Making the Connections - Appendix


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, $

FILENAME=EMPDATA, SUFFIX=FOC

SEGNAME=EMPDATA, SEGTYPE=S1


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, $


JOIN, MATCH and MOREEMPDATA - Data

PIN LASTNAME FIRSTNAME --- -------- --------- 000000010 VALINO DANIEL 000000020 BELLA MICHAEL 000000030 CASSANOVA LOIS 000000040 ADAMS RUTH 000000050 ADDAMS PETER 000000060 PATEL DORINA 000000070 SANCHEZ EVELYN 000000080 SO PAMELA 000000090 PULASKI MARIANNE 000000100 ANDERSON TIM

PIN LASTNAME FIRSTNAME --- -------- --------- 000000010 VALINO DANIEL 000000020 BELLA MICHAEL 000000030 CASSANOVA LOIS 000000040 ADAMS RUTH 000000050 ADDAMS PETER 000000060 PATEL DORINA 000000070 SANCHEZ EVELYN 000000080 SO PAMELA 000000090 PULASKI MARIANNE 000000100 ANDERSON TIM


JOIN, MATCH and MOREKids - Master

FILENAME=KIDS , SUFFIX=FOC

SEGNAME=CHILDSEG, SEGTYPE=S1

FIELDNAME=EMP_ID, ALIAS=PIN, FORMAT=A9, INDEX =I ,$

FIELDNAME=LASTNAME, ALIAS=SLN, FORMAT=A15 ,$

FIELDNAME=CHILDNAME, ALIAS=SFN, FORMAT=A10 ,$

FIELDNAME=DATE_OF_BIRTH, ALIAS=DOB, FORMAT=MDYY ,$

FILENAME=KIDS , SUFFIX=FOC

SEGNAME=CHILDSEG, SEGTYPE=S1

FIELDNAME=EMP_ID, ALIAS=PIN, FORMAT=A9, INDEX =I ,$

FIELDNAME=LASTNAME, ALIAS=SLN, FORMAT=A15 ,$

FIELDNAME=CHILDNAME, ALIAS=SFN, FORMAT=A10 ,$

FIELDNAME=DATE_OF_BIRTH, ALIAS=DOB, FORMAT=MDYY ,$


JOIN, MATCH and MOREKids - 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

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


JOIN, MATCH and MORESpice - Master

FILENAME=SPICE , SUFFIX=FOC

SEGNAME=SPOUSEI, SEGTYPE=S1


FIELDNAME=LASTNAME, ALIAS=SLN, FORMAT=A15,$

FIELDNAME=SPOUSENAME, ALIAS=SFN, FORMAT=A10,$

FIELDNAME=SPOUSESSN , ALIAS=SSN, FORMAT=A9 ,$

FILENAME=SPICE , SUFFIX=FOC

SEGNAME=SPOUSEI, SEGTYPE=S1


FIELDNAME=LASTNAME, ALIAS=SLN, FORMAT=A15,$

FIELDNAME=SPOUSENAME, ALIAS=SFN, FORMAT=A10,$

FIELDNAME=SPOUSESSN , ALIAS=SSN, FORMAT=A9 ,$


JOIN, MATCH and MORESpice - 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

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


JOIN, MATCH and MOREInsurance 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

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 1 join, match and more– making the connections walter...

Documents

data sources

joinname end equal join

joinname end join

join basic characteristics

editymd prdid join invid

file2 tag tagname

based slide

field2 tag tagname