I looked on focal point it mentions why this problem is happening. It maybe is related to the join probem; however, I am not able to find how to correct it. My join runs fine but I when creaate rport I get the following error messge.
0 ERROR AT OR NEAR LINE 76 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH
JOIN STAGE_DISCOUNT_POS.STAGE_DISCOUNT_POS.GLCODEID IN STAGE_DISCOUNT_POS TO MULTIPLE DIM_TRANSTYPE.DIM_TRANSTYPE.GLCODEID IN DIM_TRANSTYPE TAG J0 AS J0 END JOIN STAGE_DISCOUNT_POS.STAGE_DISCOUNT_POS.POSTDATE IN STAGE_DISCOUNT_POS TO MULTIPLE DIM_TIME.DIM_TIME.DATE IN DIM_TIME TAG J1 AS J1 END TABLE FILE STAGE_DISCOUNT_POS SUM 'STAGE_DISCOUNT_POS.STAGE_DISCOUNT_POS.AMOUNT' BY 'J1.DIM_TIME.FYPW' BY 'J0.DIM_TRANSTYPE.GROUPER2' BY 'J0.DIM_TRANSTYPE.GROUPER1' BY 'STAGE_DISCOUNT_POS.STAGE_DISCOUNT_POS.SCHOOLID' HEADING "" FOOTING "" WHERE J1.DIM_TIME.FYP EQ '&FYP.(FIND DIM_TIME.DIM_TIME.FYP,DIM_TIME.DIM_TIME.FYP IN dim_time).FYP.'; ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT,This message has been edited. Last edited by: Kerry,
I have struggled with this error many times I have found that the reason it does this is you have a tree structured join and are trying to sort by fields in tables that are not connected: ie you have a sort by a field in your first "branch" then sort by a field in your second "branch" then sort by a field in your "truck" table. Normally your "trunk" and your first "branch" table sorts are not the problem it is other "branches" and your branches are not connected.
A way to avoid this is put everything into a hold file and then do your sorting. Or I have also found that if your join is left-outer and multiple it will work as well.
all output (Excel, HTML, PDF)
Your join structure is a tree structure.
If you issue a CHECK FILE ... PICT its will show you the join structure.
JOIN PIN IN EMPDATA TO PIN IN PERSINFO AS J1 JOIN PIN IN EMPDATA TO PIN IN JOBHIST AS J2 CHECK FILE EMPDATA PICT
STRUCTURE OF FOCUS FILE EMPDATA ON 07/28/10 AT 08.17.15 EMPDATA 01 S1 ************** *PIN **I *LASTNAME ** *FIRSTNAME ** *MIDINITIAL ** * ** *************** ************** I +-----------------+ I I I PERSONAL I JOBHIST 02 I KU 03 I KU .............. .............. :PIN :K :PIN :K :INCAREOF : :JOBSTART : :STREETNO : :JOBCLASS : :APT : :FUNCTITLE : : : : : :............: :............:
You are using sort fields from the left and right side of the join, this causes the error.
If your sort fields were from top to botton, e.g. LASTNAME and JOBSTART, then it would be OK.
And as Malinda says, holding can help. If you don't sort, don't use BY fields from the two sides, then its will extract the data, you can then do the sorting.
You could also try using BY TOTAL.
Sometimes you can avoid the multi-path problem by rearranging your JOINs to create a single path structure. For example, you could try the code below, especially given that your WHERE clause is based in table DIM_TIME.
JOIN DIM_TIME.DATE IN DIM_TIME TO STAGE_DISCOUNT_POS.POSTDATE IN STAGE_DISCOUNT_POS AS J1 END JOIN STAGE_DISCOUNT_POS.GLCODEID IN DIM_TIME TO MULTIPLE DIM_TRANSTYPE.GLCODEID IN DIM_TRANSTYPE AS J2 END -* TABLE FILE DIM_TIME SUM 'STAGE_DISCOUNT_POS.AMOUNT' BY 'DIM_TIME.FYPW' BY 'DIM_TRANSTYPE.GROUPER2' BY 'DIM_TRANSTYPE.GROUPER1' BY 'STAGE_DISCOUNT_POS.SCHOOLID' HEADING "" FOOTING "" WHERE DIM_TIME.FYP EQ '&FYP.(FIND DIM_TIME.FYP,DIM_TIME.FYP IN dim_time).FYP.'; ON TABLE SET PAGE-NUM OFF ON TABLE NOTOTAL ON TABLE PCHOLD FORMAT HTML ON TABLE SET HTMLCSS ON ON TABLE SET STYLE * UNITS=IN, SQUEEZE=ON, ORIENTATION=PORTRAIT, . . . ENDSTYLE END
@Dan, The rearranging worked like a charm!!
Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
|Powered by Social Strata|