I have a master file that combines 11 other master files all via inner joins (similar to how I’d use an SQL view) that I base a report on. When I sort by a single column in this report I receive the following error: (FOC030) ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS. When I sort on two columns in this report, I receive the following error: (FOC029) ALL SORT KEYS ARE NOT IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH.

I created computes with the columns I want to sort on, and I was able to sort on those fine. Can anyone explain these errors, and if resolving by COMPUTEs is acceptable?

This message has been edited. Last edited by: shelzalee,

You should be JOINing in a top down perspective. Do a CHECK FILE PICTURE and see what it looks like. The sort field needs to be "IN A SINGLE TOP-TO-BOTTOM SEGMENT PATH" (in path).

I'm pretty new to WebFOCUS. I don't know what a "top down perspective" is or what "in a single top-to-bottom segment path" means. The synonym editor reminds me of a GUI tool for SQL where I can join on whatever I want and then sort by the result fields, but apparently it doesn't work that way. Below is what the CHECK FILE PICTURE came back with, but it doesn't mean much to me.

 
 0 NUMBER OF ERRORS=     0
 NUMBER OF SEGMENTS=  15  ( REAL=    0  VIRTUAL=  15 )
 NUMBER OF FIELDS=   482  INDEXES=   0  FILES=    13
 NUMBER OF DEFINES=    1
 TOTAL LENGTH OF ALL FIELDS=37794
 1SECTION 01
 STRUCTURE OF SQLMSS   FILE FACT_FG  ON 03/22/12 AT 14.12.10
 FACT_FG
 01      KM
 ..............
 :BASEITEM_SK ::
 :CUSTOMER_SK ::
 :BOL_SK      ::
 :MILLCOIL_SK ::
 :            ::
 :............::
 .............:
 I CUSTOMER_INVOICE
 +-----------------+-----------------+-----------------+----------
 I                 I                 I                 I
 I DIM_BOL         I DIM_CUS>        I DIM_FG_>        I DIM_INV>
 02    I KM        03    I KM        04    I KM        05    I KM
 ..............    ..............    ..............    ..............
 :BOL_SK      ::   :CUSTOMER_SK ::   :TAG_SK      ::   :INVOICE_SK  ::
 :LOCATIONNUM>::   :CUSTOMERNUM>::   :TAG         ::   :INVOICENUMB>::
 :BOLNUMBER   ::   :SOLDTOCUSTN>::   :SERIALNUMBER::   :SHOPORDERNU>::
 :FOBCODE     ::   :SOLDTOADDR1 ::   :LOCATIONNUM>::   :LOCATIONNUM>::
 :            ::   :            ::   :            ::   :            ::
 :............::   :............::   :............::   :............::
 .............:    .............:    .............:    .............:
 CUSTOMER_INVOICE  CUSTOMER_INVOICE  CUSTOMER_INVOICEI CUSTOMER_
 I
 I
 I FACT_IN>
 06    I KU
 ..............
 :INVOICEDATE :
 :PRODUCTION_>:
 :INVOICE_SK  :
 :LOADDATE    :
 :            :
 :............:
 I CUSTOMER_
 I
 +-----------------------------------+----------
 I                                   I
 I DIM_MAT>                          I DIM_INV>
 07    I KM                          08    I KM
 ..............                      ..............
 :MATERIAL_TY>::                     :FULL_DATE   ::
 :MATERIALTYPE::                     :DATE_KEY    ::
 :MATERIALTYP>::                     :DATE_TYPE   ::
 :BEGINDATE   ::                     :DAY_NUM_OF_>::
 :            ::                     :            ::
 :............::                     :............::
 .............:                      .............:
 CUSTOMER_INVOICE                    CUSTOMER_
 1SECTION 02
 -------+-----------------+-----------------+-----------------+----------
 I                 I                 I                 I
 I DIM_LOC>        I DIM_SHI>        I DIM_SHOP        I DIM_PRO>
 10    I KM        11    I KM        12    I KM        13    I KM
 ..............    ..............    ..............    ..............
 :LOCATION_SK ::   :FULL_DATE   ::   :SHOP_SK     ::   :PRODSHOP_SK ::
 :LOCATIONNUM>::   :DATE_KEY    ::   :SHOPORDERNU>::   :COMPANYNUMB>::
 :LOCATIONNAME::   :DATE_TYPE   ::   :LOCATIONNUM>::   :LOCATIONNUM>::
 :LOCATIONTYPE::   :DAY_NUM_OF_>::   :CUSTOMERPON>::   :PRODUCTIONN>::
 :            ::   :            ::   :            ::   :            ::
 :............::   :............::   :............::   :............::
 .............:    .............:    .............:    .............:
 CUSTOMER_INVOICE  CUSTOMER_INVOICE  CUSTOMER_INVOICE  CUSTOMER_
 -------+
 I
 I DIM_PAI>
 09    I KM
 ..............
 :FULL_DATE   ::
 :DATE_KEY    ::
 :DATE_TYPE   ::
 :DAY_NUM_OF_>::
 :            ::
 :............::
 .............:
 CUSTOMER_INVOICE
 1SECTION 03
 -------+-----------------+
 I                 I
 I DIM_PRO>        I DIM_MIL>
 14    I KM        15    I KM
 ..............    ..............
 :PRODUCTION_>::   :MILLCOIL_SK ::
 :PRODUCTIONN>::   :MILLCOILNUM>::
 :LOCATIONNUM>::   :LOCATIONNUM>::
 :INITIALSETU>::   :SHOPORDERNU>::
 :            ::   :            ::
 :............::   :............::
 .............:    .............:
 CUSTOMER_INVOICE  CUSTOMER_INVOICE

 

I think you may have to post your master, as the CHECK FILE PICT is being left justified.

The important thing about the top down, is that you start with the top segment, and all following segments come from it like children.

If you have two children, and each has children (Your grand children), then to get info from grand child 1 of child one, you must go through child one to access its children. (FOC030)

(FOC029) happens when you sort fields are not in the segment order of you/child/grandchild.

There are ways around this.

For sorting, one option is to extract/hold, then do what you want. Another is to sort BY TOTAL field.

For the (FOC030) issue, I think there is a SET command that allows the reporting of multiple segments, don't remember what it is though.

[SOLVED]

Based on Doug's post earlier, I did segment path research, and found that in my case I could use a "JOIN Unique" which makes all master files be treated as though they are in the same path, resolving my error.