1 / 51

JOIN, MATCH and MORE– Making the Connections

JOIN, MATCH and MORE– Making the Connections. Walter F. Blood Technical Director, FOCUS Division May, 2011. DepartmentData. Product Data. Employee Data. Customer Data. Job Data. Invoices. Purchase Orders. Support Data. Inventory Data. JOIN, MATCH and MORE Basic Choices.

didier
Download Presentation

JOIN, MATCH and MORE– Making the Connections

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. JOIN, MATCH and MORE– Making the Connections Walter F. Blood Technical Director, FOCUS Division May, 2011

  2. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN, MATCH and MOREBasic Choices When your report requires data from many different sources… …you need JOIN, MATCH or MORE to make the connections!

  3. 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:

  4. JOIN, MATCH and MOREJOIN – the Nested Loop Nested Loop A B C

  5. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN, MATCH and MOREJOIN - Basic Characteristics • 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 treats multiple structures as one

  6. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN , MATCH and MOREEqual JOIN Syntax LONG FORM JOIN field1 [ AND field2 …] [TAG tagname] IN file1 TO [ALL] fielda [AND fieldb…] IN file2 [TAG tagname] AS joinname END SHORT FORM JOIN field1 IN file1 TO [ALL] field2 IN file2 AS name JOIN EMPID IN EMPLOYEE TO EMPID IN DEPARTMENT AS JEMP

  7. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data 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 JOIN INVID WITH PRDID IN PRODUCT TO INVID IN INVOICES AS INV DEFINE FILE PRODUCT INVID/A25=EDIT(&YMD) | PRDID; …

  8. 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

  9. JOIN, MATCH and MOREEqual 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

  10. JOIN – Making the ConnectionEqual 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

  11. 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

  12. 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

  13. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data 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 JOIN FILE EMPLOYEE AT EMPID TO ALL FILE CAR AT SEATS AS J1 WHERE RETAIL_COST LE SALARY * 3; END

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

  15. 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

  16. JOIN, MATCH and MOREConditional 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 HEADING "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 Less than Maximum Age

  17. 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: 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.

  18. 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)

  19. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN, MATCH and MOREJOINS – One to Many Relationships JOIN PIN IN EMPDATA TO ALL PIN IN KIDS AS JOIN1 END OUTER INNER KIDS EMPDATA EMP_ID LASTNAME CHILDNAME MIDINITIAL PIN LASTNAME FIRSTNAME MIDINITIAL

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

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

  22. 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 .

  23. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN, MATCH and MORE JOIN - Unique Relationship JOIN PIN IN EMPDATA TO PIN IN SPICE AS JOIN1 END SPICE EMPDATA PIN LASTNAME FIRSTNAME MIDINITIAL PIN LASTNAME SPOUSENAME SSN

  24. 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 --- 000000010 000000030 000000040 000000060 000000070 000000090 EMP_ID ------ 000000010 000000010 000000010 000000010 000000030 000000040 000000060 000000070

  25. 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 Blanks

  26. JOIN, MATCH and MOREMATCH – Sort Merge Sort Merge B A C

  27. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN, MATCH and MOREMATCH - Basic Characteristics • 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 merges files sequentially one at a time

  28. BY fields connect files Merge phrase selects JOIN, MATCH and MOREMATCH - 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

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

  30. 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.

  31. 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)

  32. JOIN, MATCH and MOREMATCH – Fine-Tuning 2 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 N Country DCost 1 England 1000 1 England 2000 2 Japan 3000 2 Japan 4000 SUM  SUM 1 England 3000Jensen 11000 2 Japan 7000Toyota 11000 PRINT  SUM 1 England 2000Jensen 11000 2 Japan 4000Toyota 11000 SUM  PRINT 1 England 3000Jaguar 5000 1 England 3000Jensen 6000 2 Japan 7000Datsun 4000 2 Japan 7000Toyota 7000 PRINT  PRINT 1 England 1000Jaguar 5000 1 England 2000Jensen 6000 2 Japan 3000Datsun 4000 2 Japan 4000Toyota 7000

  33. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN, MATCH and MOREMORE - Basic Characteristics • 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 MORE concatenates files sequentially one at a time

  34. JOIN, MATCH and MOREMORE - Basic Characteristics • 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

  35. JOIN, MATCH and MOREMORE 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

  36. FOCUS 1 DATA VSAM 2 DATA OUTPUT SORT DATA FIX 3 JOIN, MATCH and MOREMORE Processing Order of processing:

  37. FOCUS VSAM FIX Data Retrieval(DEFINE/IF) Data Retrieval(DEFINE/IF) Data Retrieval(DEFINE/IF) MORE MORE EOF Y N EOF Y N EOF Y N FOCSORT COMPUTE/IF TOTAL OUTPUT JOIN, MATCH and MOREMORE Processing FIX

  38. VSAM • File 2 • Subrequest • ONLY WHERE/IF syntax • File 3 • Subrequest • ONLY WHERE/IF syntax FIX JOIN, MATCH and MOREMORE Processing FOCUS • File 1 • Main Request • Supported TABLE, GRAPH, MATCH FILE syntax e.g. BY WHERE, IF, COMPUTE

  39. JOIN, MATCH and MOREMORE 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)

  40. JOIN, MATCH and MOREMORE 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

  41. JOIN, MATCH and MOREMORE 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

  42. JOIN, MATCH and MOREWhich one? Which One Do We Use and Why

  43. DepartmentData Product Data Employee Data Customer Data Job Data Invoices Purchase Orders Support Data Inventory Data JOIN, MATCH and MORE Making the Connections Thank You

  44. JOIN, MATCH and MORE Making the Connections - Appendix Appendix

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

  46. 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

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

  48. 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

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

  50. 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

More Related