Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] (FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] (FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH
 Login/Join
 
Guru
posted
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,


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
I have struggled with this error many times Smiler 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


WebFOCUS 7.6.11
Windows
all output (Excel, HTML, PDF)
 
Posts: 149 | Registered: April 15, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.05OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6053 | Location: Land of the Darug people, Terra Australis Incognita | Registered: October 31, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
@Dan, The rearranging worked like a charm!!
Thanks.


Webfocus 8105,8808,7703,7611, EXL2K,HTML,PDF,COMT,AHTML Info Assist+ , Reportcaster
 
Posts: 54 | Location: Toronto, Ontario | Registered: May 15, 2014Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] (FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.