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.

Malinda

Your join structure is a tree structure.

If you issue a CHECK FILE ... PICT its will show you the join structure.

e.g.

JOIN PIN IN EMPDATA TO PIN IN PERSINFO AS J1
JOIN PIN IN EMPDATA TO PIN IN JOBHIST  AS J2

CHECK FILE EMPDATA PICT

Output:

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!!
Thanks.