1 / 50

Reporting Internals From a Reports Point of View

Reporting Internals From a Reports Point of View. Renee Teatro Customer Support Services. Presentation Information. Author: Cesare Petrizio Company: Information Builders Presentation Title: Reporting Internals From a Reports Point of View

lacey
Download Presentation

Reporting Internals From a Reports Point of View

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. Reporting Internals From a Reports Point of View Renee Teatro Customer Support Services

  2. Presentation Information • Author: Cesare Petrizio • Company: Information Builders • Presentation Title: Reporting Internals From a Reports Point of View • Abstract: This presentation looks at what FOCUS is doing under the covers when a TABLE is executed in FOCUS on any platform, or through WEBFOCUS. Also, the TABLE could be against a FOCUS file, MSS TABLE, VSAM, etc. Application developers can create more efficient requests when they understand how FOCUS sets up to retrieve data from FOCUS files, Relational TABLEs, and other non-FOCUS data sources. Learn the answers to such questions as: When would an index be more efficient? One pass or two? DEFINE vs. COMPUTE? What is AUTOPATH and AUTOINDEX, and what do they mean in terms of efficiency?

  3. Agenda • Reporting Internals • Retrieval Internals • Analysis Phase • Retrieval Phase • Internal Matrix Generation q

  4. Analysis Phase • Open and CHECK Master File Descriptions • Verify DBA access to file(s) • Determine I/O access modules required q

  5. Analysis Phase The CHECK FILE CHECK FILE EMPLOYEE NUMBER OF ERRORS= 0 NUMBER OF SEGMENTS= 2 ( REAL= 2 VIRTUAL= 0 ) NUMBER OF FIELDS= 6 INDEXES= 0 FILES= 1 TOTAL LENGTH OF ALL FIELDS= 46 CHECK FILE KIDS NUMBER OF ERRORS= 0 NUMBER OF SEGMENTS= 1 ( REAL= 1 VIRTUAL= 0 ) NUMBER OF FIELDS= 3 INDEXES= 0 FILES= 1 TOTAL LENGTH OF ALL FIELDS= 33 CHECK FILE SPICE NUMBER OF ERRORS= 0 NUMBER OF SEGMENTS= 1 ( REAL= 1 VIRTUAL= 0 ) NUMBER OF FIELDS= 2 INDEXES= 1 FILES= 1 TOTAL LENGTH OF ALL FIELDS= 29 • Is This A Good Sign? q

  6. Analysis Phase The Files FILE=KIDS,SUFFIX=SQLMSS SEGNAME=SEGK,SEGTYPE=S2 FIELD=EMP_ID, EID ,A9,$ FIELD=CHILD_DOB, CDOB ,YYMD,$ FIELD=CHILD_NAME, CNAME ,A20,$ FILE=EMPLOYEE,SUFFIX=FOC SEGNAME=SEG1,SEGTYPE=S1 FIELD=EMP_ID, EID,A9,$ FIELD=FIRST_NAME, FN,A15,$ FIELD=LAST_NAME, LN,A15,$ SEGNAME=INSSEG,SEGTYPE=SH2 FIELD=COVER_DT, ,YMD,$ FIELD=TYPE_COVER, ,A3,$ FIELD=FAMILY, ,A1,$ FILE=SPICE,SUFFIX=XFOC SEGNAME=SEGS,SEGTYPE=S1 FIELD=EMP_ID, EID, A9, INDEX=I,$ FIELD=SPOUSE,SPOUSE_NME,A20,$ • Any DBA Specified? • What Type Of File Access Method(s) q

  7. Report Generator Retrieval Internals Retrieve DEFINE JOIN Screening Analysis FOCUS or External Sort Aggregation COUNT/MIN/MAX Sort File RETYPE REPLOT HOLD TOTAL Screens COMPUTE Format Extracts Output Generator q

  8. Retrieval InternalsThe Files XFOCUS Are Any Of The Following Different? • DEFINEs • Selection Test • JOINs FILE=SPICE,SUFFIX=FOC SEGNAME=SEGS,SEGTYPE=S1 FIELD=EMP_ID, EID, A9, INDEX=I,$ FIELD=SPOUSE,SPOUSE_NME,A20,$ FILE=SPICE,SUFFIX=XFOC SEGNAME=SEGS,SEGTYPE=S1 FIELD=EMP_ID, EID, A9, INDEX=I,$ FIELD=SPOUSE,SPOUSE_NME,A20,$ FILE=SPICE,SUFFIX= SQLMSS SEGNAME=SEGS,SEGTYPE=S0 FIELD=EMP_ID, EID, A9, A9 ,$ FIELD=SPOUSE,SPOUSE_NME,A20 ,A20,$ q

  9. JOIN EMP_ID IN EMPLOYEE TO EMP_ID IN SPICE AS AJ JOIN EMP_ID IN EMPLOYEE TO ALL EMP_ID IN KIDS AS BJ EMP_ID FIRST_NAME LAST_NAME KU KM COVER_DT TYPE_COVER FAMILY EMP_ID SPOUSE EMP_ID CHILD_DOB CHILD_NAME K K Retrieval InternalsExample SH2 q

  10. JOIN SYNTAX JOIN field1 [WITH rfield] IN hostfile [TAG tag1] TO [ALL] field2 IN crfile [TAG tag2] [AS name][END] • where: • field1, field2 - Are the fields common to both Master Files. • WITH rfield1 - Use only if field1 is a virtual field; assigns a logical home with a real field in the host file. • hostfile - Is the host Master File. • TAG tag1 - Is a tag name of up to eight characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in the host file. The tag name for the host file must be the same in all the JOIN commands of a joined structure. • ALL - Use if non-unique relationships exist in the target file. • crfile - Is the target or cross-referenced Master File. • TAG tag2 - Is a tag name of up to eight characters (usually the name of the Master File), which is used as a unique qualifier for fields and aliases in the cross-referenced file. In a recursive joined structure, if no tag name is provided, all field names and aliases are prefixed with the first four characters of the join name. • AS name - Assigns a name to the JOIN structure. You must assign a unique name to a join structure if: • You want to ensure that a subsequent JOIN command will not overwrite it. • You want to clear it selectively later. • The structure is recursive, and you do not specify tag names. • END q

  11. Retrieval InternalsExample JOIN EMP_ID IN EMPLOYEE TO EMP_ID IN SPICE AS AJ JOIN EMP_ID IN EMPLOYEE TO ALL EMPID IN KIDS AS BJ RETRIEVAL VIEW OF FOCJNT FILE EMPLOYEE ON 03/16/09 AT 09.04.14 SEG01 01 S1 ************** *PIN ** *FIRSTNAME ** *LASTNAME ** * ** * ** *************** ************** I I I I SEGS 02 I KU .............. :EMP_ID :K :SPOUSE : : : : : : : :............: JOINEDI SPICE I +-----------------+ I I I KIDS01 I INSSEG 03 I KM 04 I SH2 .............. ************** :EMP_ID ::K *COVER_DATE ** :CHILD_DOB :: *TYPE_COVER ** :CHILD_NAME :: *FAMILY ** : :: * ** : :: * ** :............:: *************** .............: ************** JOINED KIDS STRUCTURE OF FOCJNT FILE EMPLOYEE ON 03/16/09 AT 09.05.56 SEG01 01 S1 ************** *PIN ** *FIRSTNAME ** *LASTNAME ** * ** * ** *************** ************** I +-----------------+-----------------+ I I I I SEGS I KIDS01 I INSSEG 02 I KU 03 I KM 04 I SH2 .............. .............. ************** :EMP_ID :K :EMP_ID ::K *COVER_DATE ** :SPOUSE : :CHILD_DOB :: *TYPE_COVER ** : : :CHILD_NAME :: *FAMILY ** : : : :: * ** : : : :: * ** :............: :............:: *************** JOINED SPICE .............: ************** JOINED KIDS q

  12. Retrieval InternalsExample SET ALL=ONJOIN EMP_ID IN EMPLOYEE TO EMP_ID IN SPICE AS AJ JOIN EMP_ID IN EMPLOYEE TO ALL EMPID IN KIDS AS BJ TABLE FILE EMPLOYEE PRINT * END q

  13. Updated JOIN syntax JOIN [LEFT_OUTER|INNER] hfld1 [AND hfld2 ...] IN hostfile [TAG tag1] TO [UNIQUE|MULTIPLE] crfield [AND crfld2 ...] IN crfile [TAG tag2] [AS joinname] END where: INNER - LEFT_OUTER - Specifies an left outer join or inner join. hfld1 - AND hfld2... Is the name of a field(s) in the host file IN hostfile - Is the name of the host file. TAG tag1 - is a tag name of up to eight characters which is used as a unique qualifier for fields TO [UNIQUE|MULTIPLE] crfld1 - Is the name of a field in the cross-referenced file AND crfld2... - Is the name of a field in the cross-referenced file with values in common with hfld2. IN crfile - Is the name of the cross-referenced file. TAG tag2 - Is a tag name of up to eight characters AS joinname - Is an optional name of up to eight characters that you may assign to the join structure. q

  14. Analysis • Retrieve DEFINE field expressions for pertinent files • Read and parse the request • Check DBA and set up access restrictions at SEGMENT and FIELD levels q

  15. DEFINE • Segment is determined by: • WITH field • Fields used in expression • Constant expressions are evaluated when file is opened (segment 0) • DEFINEs are evaluated ONLY if required by request • DEFINEs are evaluated at detail level only • Each DEFINE field is associated with a specific segment q

  16. Analysis • Activate segments: referenced subtree(s) – smallest subtree which contains “root” and every segment referenced directly or indirectly (HEADING PARENT AND GRAND CHILD) • Activate fields (real and DEFINEd) • Read USE list and/or check Access Files and FILEDEF/ALLOCATES • Open and Verify files • Invoke the Data adapter q

  17. Retrieval InternalsThe Files KIDS EMPLOYEE SPICE SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I I I I INSSEG 02 I SH2 ************** *COVER_DT ** *TYPE_COVER ** *FAMILY ** * ** * ** *************** ************** SEGS 01 S1 ************** *EMP_ID **I *SPOUSE ** * ** * ** * ** *************** ************** SEGK 01 S2 ************** *EMP_ID ** *CHILD_DOB ** *CHILD_NAME ** * ** * ** *************** ************** q

  18. Retrieval Internals SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I +-----------------+-----------------+ I I I I SEGS I SEGK I INSSEG 02 I KU 03 I KM 04 I SH2 .............. .............. ************** :EMP_ID :K :EMP_ID ::K *COVER_DT ** :SPOUSE : :CHILD_DOB :: *TYPE_COVER ** : : :CHILD_NAME :: *FAMILY ** : : : :: * ** : : : :: * ** :............: :............:: *************** JOINED SPICE .............: ************** JOINED KIDS SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I +-----------------+ I I I SEGS I INSSEG 02 I KU 03 I SH2 .............. ************** :EMP_ID :K *COVER_DT ** :SPOUSE : *TYPE_COVER ** : : *FAMILY ** : : * ** : : * ** :............: *************** JOINED SPICE ************** JOIN EMP_ID IN EMPLOYEE TO EMP_ID IN SPICE AS J1 JOIN EMP_ID IN EMPLOYEE TO ALL EMP_ID IN KIDS AS J2 q

  19. Retrieval Internals Seg 4 Define Based on TYPE_COVER Seg 0 Define (No Reference) Seg 3 Define Based on CHILD_DOB DEFINE FILE EMPLOYEE INS_TYPE/A6 = DECODE TYPE_COVER( DDS 'DENTAL' EYE 'VISION' MDS 'HEALTH' ELSE 'OTHER'); TODAY/YYMD = '&YYMD'; AGE/I3 = (TODAY - CHILD_DOB) / 365.25; END TABLE FILE EMPLOYEE PRINT INS_TYPE CHILD_NAME AGE BY LAST_NAME BY FIRST_NAME BY SPOUSE IF AGE LE 20 END q

  20. Retrieval InternalsSegment Activation TABLE FILE EMPLOYEE PRINT INS_TYPE CHILD_NAME AGE BY LAST_NAME BY FIRST_NAME BY SPOUSE IF AGE LE 20 END SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I +-----------------+-----------------+ I I I I SEGS I SEGK I INSSEG 02 I KU 03 I KM 04 I SH2 .............. .............. ************** :EMP_ID :K :EMP_ID ::K *COVER_DT ** :SPOUSE : :CHILD_DOB :: *TYPE_COVER ** : : :CHILD_NAME :: *FAMILY ** : : : :: * ** : : : :: * ** :............: :............:: *************** JOINED SPICE .............: ************** JOINED KIDS Seg 1 Activated by FIRST_NAME and LASTNAME Seg 2 Activated by SPOUSE Seg 3 Activated by CHILD_DOB and CHILD_NAME Seg 4 Activated by TYPE_COVER Via INS_TYPE DEFINE q

  21. Retrieval InternalsField Activation DEFINE FILE EMPLOYEE INS_TYPE/A6 = DECODE TYPE_COVER( DDS 'DENTAL' EYE 'VISION' MDS 'HEALTH' ELSE 'OTHER'); TODAY/YYMD = '&YYMD'; AGE/I3 = (TODAY - CHILD_DOB) / 365.25; END TABLE FILE EMPLOYEE PRINT INS_TYPE CHILD_NAME AGE BY LAST_NAME BY FIRST_NAME BY SPOUSE IFAGE LE 20 END • Fields Mentioned in the Body of TABLE are ACTIVATED • FIRST_NAME • LASTNAME • SPOUSE • CHILD_NAME • CHILD_DOB (ViaAGEDEFINE) • TYPE_COVER (Via INS_TYPE DEFINE) q

  22. Retrieval • Retrieval is “Top Down, Left Right” within the referencedsub-tree • Unique segments are promoted into path of parent and are never missing • IF/WHERE tests on database fields are evaluated upon retrieval • DEFINE fields are evaluated if needed • IF/WHERE tests are evaluated on DEFINE fields on segment by segment basis, AFTER all other selection tests are passed • Each Path is processed independently, and fields are merged on common sort fields q

  23. Retrieval Order of Retrieval – FOCUS & XFOCUS Files • Root segment instances are obtained in physical order • Lower level instances obtained in SEGTYPE order within parent segment • TABLEF • Root and child segment instances are obtained in SEGTYPE order • No sorting • TABLE/GRAPH/MATCH q

  24. Retrieval Order of Retrieval – FOCUS Files • When Fieldname is not indexed • Segment containing “fieldname” becomes ROOT of the view, and is retrieved physically • When Fieldname is indexed • Segment containing “fieldname” is accessed via the equality test on INDEX • Other segments become children of the new root segment • TABLE FILE filename.fieldname q

  25. Retrieval InternalsAUTOPATH - Two Masters And How It Treats Seg1 JOIN EMP_ID IN EMPLOYEE TO EMP_ID IN SPICE AS J1 SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I + I I SEGS 02 I KU .............. :EMP_ID :K :SPOUSE : : : : : : : :............: JOINED SPICE FILE=EMPLOYEE,SUFFIX=SQL SEGNAME=SEG1,SEGTYPE=S0 FIELD=EMP_ID, EID,A9,$ FIELD=FIRST_NAME, FN,A15,$ FIELD=LAST_NAME, LN,A15,$ TABLE FILE EMPLOYEE PRINT SPOUSE END HOW MANY SEGMENTS DO WE READ? 2 Root SEG always gets read FILE=SPICE,SUFFIX=SQL SEGNAME=SEGS,SEGTYPE=S0 FIELD=EMP_ID, EID, A9, $ FIELD=SPOUSE,SPOUSE_NME,A20,$ q

  26. Retrieval InternalsAUTOPATH - Single Master And How It Treats Seg1 SEG1 01 S1 ************** *EMP_ID ** *FIRST_NAME ** *LAST_NAME ** * ** * ** *************** ************** I + I I SEGS 02 I KU .............. :EMP_ID :K :SPOUSE : : : : : : : :............: JOINED SPICE TABLE FILE EMPLOYEE PRINT SPOUSE END FILE=EMPLOYEE,SUFFIX=SQL SEGNAME=SEG1,SEGTYPE=S0 FIELD=EMP_ID, EID,A9,$ FIELD=FIRST_NAME, FN,A15,$ FIELD=LAST_NAME, LN,A15,$ SEGNAME=SEGS,SEGTYPE=S0, PARENT =SEG1, FIELD=EMP_ID, EID, A9, $ FIELD=SPOUSE,SPOUSE_NME,A20,$ HOW MANY SEGMENTS DO WE READ? 1 SEGS - AUTOPATH q

  27. Retrieval Order of Retrieval • AUTOPATH = ON • Alternate Physical View is created through the referenced segment highest in the hierarchy • AUTOINDEX =ON • Alternate Indexed View is created if there is an Equality test specified for an indexed field on the referenced segment highest in the hierarchy • AUTOSTRATEGY = ON • Determines when FOCUS stops the search for a key field specified in a WHERE or IF test. When set to ON, the search ends when the key field is found • FIXRETRIEVE = ON • Equality IF/WHERE test on primary specified sort field in single segment FIX file will terminate retrieval as soon as possible q

  28. 1 STATE=IL, DATE=2002 Continue Search 2 STATE=IL, DATE=2001 Continue Search 3 STATE=IL, DATE=1999 Continue Search 4 STATE=IL, DATE=1998 Next Parent 5 STATE=MI, DATE=1999 Next Parent IL MI 1996 1997 1997 1998 2001 2002 1998 1999 Retrieval Autostrategy – FOCUS Files WHERE DATE EQ ‘1999’ q

  29. Retrieval Stops When: SET FIXRETRIEVE = ON FIXED Files ENGLAND JAGUAR ENGLAND JENSEN ENGLAND TRIUMPH FRANCE PEUGEOT ITALY AUDI JAPAN…… WHERE COUNTRY EQ ‘FRANCE’ q

  30. Retrieval Multiple Paths • Retrieval is performed for each path separately • Unique segments are always in the path of their parent • Unique segments are SEGTYPE = U, KU, DKU, KLU • JOIN TO creates DKU segments • JOIN TO ALL creates DKM segments • TABLE/MATCH/GRAPH • Instances from each path will be merged in the sort process by the common parent(s) • TABLEF does not merge data from multiple paths • Alternate file views can be used to create single paths • SET MULTIPATH controls multiple path retrieval q

  31. Multiple Paths Airport DEPART_TIME DEP_FLIGHT DEPART_TO ARRIVE_TIME ARR_FLIGHT ARR_FROM q

  32. Multiple Paths ARRIVE_TIME ARR_FLIGHT ARR_FROM DEFINE FILE AIRPORTS.ARRIVE_TIME DELAY=DEPART_TIME – ARRIVE_TIME; END TABLE FILE AIRPORTS.ARRIVE_TIME PRINT DEP_FLIGHT DEPART_TIME WHERE AIRPORT EQ ‘O’’HARE’ WHERE DELAY GT 60 WHERE DEPART_TO EQ ‘LAX’ WHERE ARR_FROM EQ ‘TOR’ END Airport DEPART_TIME DEP_FLIGHT DEPART_TO q

  33. Multiple Paths SEGTOP 01 S1 ************** *FAMILY ** * ** * ** * ** * ** *************** ************** I +-----------------+ I I I SEG1 I SEG2 02 I S103 I S1 ************** ************** *COLLEGE ***INVESTMENT ** * *** ** * *** ** * *** ** * *** ** *************** *************** q

  34. Retrieval Multiple Paths Instances Actual Data Layout Family College Investment SEGTOP 01 S1 ************** *FAMILY ** * ** * ** * ** * ** *************** ************** I +-----------------+ I I I SEG1 I SEG2 02 I S103 I S1 ************** ************** *COLLEGE ***INVESTMENT ** * *** ** * *** ** * *** ** * *** ** *************** *************** q

  35. Multiple Paths SEGTOP 01 S1 ************** *FAMILY ** * ** * ** * ** * ** *************** ************** I +-----------------+ I I I SEG1 I SEG2 02 I S103 I S1 ************** ************** *COLLEGE ***INVESTMENT ** * *** ** * *** ** * *** ** * *** ** *************** *************** TABLE FILE SAMPLE " SET MULTIPATH= &MULTIPATH " PRINT FAMILY COLLEGE INVESTMENT END q

  36. Retrieval Short Paths • High level segments with missing referenced descendents rejected (short path) • SET ALL=ON • High level segments with missing referenced descendents accepted (missing fails IF/WHERE tests) • SET ALL=PASS • High level segments with qualified or missing referenced descendents accepted (missing passes IF/WHERE tests) • Valid only for FOCUS files • SET ALL=SQL (coming in 7.7) • SET ALL=OFF q

  37. C A B C A B DEPT 1 2 2 1 2 2 C A B C A B &ALL&IFRESULT PASS SSN EQ 1 A 1, B . &ALL&IFRESULT ON SSN EQ 1 A 1 &ALL&IFRESULT OFF A 1, A 2, C 2 &ALL&IFRESULT ON A 1, A 2, B .,C 2 1 2 2 1 2 2 Retrieval Short Paths SSN SEX LNAME I I SET ALL = &ALL TABLE FILE DEPTS PRINT * &IF END q

  38. C A B C A B DEPT SSN SEX LNAME I I 1 2 2 1 2 2 Retrieval Short Paths SET ALL=PASS TABLE FILE DEPTS PRINT DEPT SSN WHERE SSN EQ 1 AND SSN EQ 2 END q

  39. RetrievalJOIN TO MULTIPLE INNER JOIN INNER key IN filea TO MULTIPLE key in fileb AS J1 • OR SET ALL = OFF JOIN key IN filea TO MULTIPLE key in fileb AS J1 LEFT OUTER JOIN LEFT_OUTER key IN filea TO MULTIPLE key in fileb AS J1 OR SET ALL =ON JOIN key IN filea TO MULTIPLE key in fileb AS J1

  40. RetrievalJOIN TO UNIQUE FOCUS MANAGED JOIN • SET ALL= IRRELEVANT • ALWAYS A LEFT OUTER JOIN • ONLY ONE MATCHING RECORD RETRIEVED • MISSING RECORD ARE DISPLAYED WITH DEFAULT VALUES ADAPTER (RELATIONAL) MANAGED JOIN • SET ALL= RELEVANT • MAY BE LEFT OUTER JOIN OR INNER JOIN BASED ON ALL • ALL=ON LEFT_OUTER • ALL=OFF INNER • ALL MATCHING RECORD RETRIEVED • MISSING RECORD ARE DISPLAYED WITH DEFAULT VALUES

  41. C A B C A B DEPT C 1 A 1 A 2 C 1 A 1 A 2 RetrievalShort Paths SSN DEPT LNAME I I JOIN DEPT IN DEPT1 TO DEPT IN EMP AS AJ TABLE FILE DEPT1 PRINT DEPT WHERE DEPT1.DEPT NE EMP.DEPT END q

  42. Retrieval Short Paths • Referenced, not present, Unique Segments are defaulted to blanks for alpha fields, zeros for numeric's • Unique segments do not create short paths. Therefore, ALL settings are irrelevant for Unique segments. • Unique segments (U, KU, DKU, and KLU) are never considered missing q

  43. FOCWIZARDISM EMPDATA 01 S1 ************** *PIN **I *LASTNAME ** *FIRSTNAME ** *MIDINITIAL ** * ** *************** ************** I I I I PERSONAL 02 I KU .............. :PIN :K :INCAREOF : :STREETNO : :APT : :STATE :I :............: JOINEDI PERSINFO I I I I ONE 03 I KU .............. :STATE :K :FULLNAME : : : : : : : :............: JOINED STATENAME Missing Referenced Descendents Of Missing Unique Segments Revert To: ALL=OFF logic If This Segment Is Missing Can’t get here q

  44. Internal Matrix Generation • One row for each distinct combination of sort keys • One column for each verb object • Computed columns, row and column totals, subtotals, and summaries are not yet done • All data is in internal form • The output of the SORT/MERGE phase is conceptually a matrix q

  45. Internal Matrix Generation • Verb Objects (both SUM and COUNT are calculated) • Fields following a verb • Fields used in COMPUTEs not previously mentioned • Fields used in HEADINGs or FOOTINGs • Fields used in SUBHEADs or SUBFOOTs, not previously mentioned • MISSING fields are not counted • Sort Fields • BY fields • ACROSS fields (used as BY at sort phase) • FOCLIST (verb is PRINT or LIST) q

  46. Internal Matrix GenerationMultiple Verb Sets SUM SALES BY DIVISION SUM SALES BY DIVISION BY YEAR LIST SALES BY DIVISION BY YEAR q

  47. SUM SALES BY DIVISION BY YEAR LIST SALES BY DIVISION BY YEAR FOCLIST SALES YEAR SALES Internal Matrix GenerationMultiple Verb Sets SUM SALES BY DIVISION DIV SALES q

  48. Multi verb option - WITHIN • Use WITHIN Phrase To Manipulate Field's Values Aggregated Within BY • Can Be Used With A Prefix Operator • Up To 64 Fields In A Display Command When Using The WITHIN Phrase • WITHIN Phrase Requires A BY Phrase And/Or An ACROSS Phrase • A Maximum Of Two WITHIN Phrases Can Be Used Per Display Field • If One WITHIN Phrase Is Used, It Must Act On A BY Phrase • If Two WITHIN Phrases Are Used • The First Must Act On A By Phrase • The Second On An ACROSS Phrase. • You Can Also Use WITHIN table • Returns The Value WITHIN A Request • WITHIN Table Can Be Used When An ACROSS Phrase Is Needed Without A BY Phrase • A Single WITHIN Phrase Requires A BY Phrase q

  49. Multi verb option - WITHIN TABLE FILE CAR SUM CNT.SALES AS 'COUNT,OF,MODELS,WITHIN,CAR' CNT.SALES WITHIN COUNTRY AS 'TOTAL,MODELS,WITHIN,COUNTRY' PCT.CNT.SALES WITHIN COUNTRY AS 'PERCENT,OF,MODELS,WITHIN,COUNTRY' CNT.SALES WITHIN TABLE AS 'TOTAL,COUNT,OF,MODELS,ALL COUNTRIES' PCT.CNT.SALES WITHIN TABLE AS 'PERCENT,OF,MODELS,ALL COUNTRIES' BY COUNTRY BY CAR END COUNT PERCENT TOTAL OF TOTAL OF COUNT PERCENT MODELS MODELS MODELS OF OF WITHIN WITHIN WITHIN MODELS MODELS COUNTRY CAR CAR COUNTRY COUNTRY ALL COUNTRIES ALL COUNTRIES ------- --- ------ ------- ------- ------------- ------------- ENGLAND JAGUAR 2 4 50.00 18 11.11 JENSEN 1 4 25.00 18 5.56 TRIUMPH 1 4 25.00 18 5.56 FRANCE PEUGEOT 1 1 100.00 18 5.56 ITALY ALFA ROMEO 3 4 75.00 18 16.67 MASERATI 1 4 25.00 18 5.56 JAPAN DATSUN 1 2 50.00 18 5.56 TOYOTA 1 2 50.00 18 5.56 W GERMANY AUDI 1 7 14.29 18 5.56 BMW 6 7 85.71 18 33.33 q

  50. Questions Thanks for Coming

More Related