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,
7.7 Windows HTML, PDF, Excel
March 22, 2012, 02:05 PM
Doug
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).
March 22, 2012, 02:22 PM
shelzalee
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
7.7 Windows HTML, PDF, Excel
March 22, 2012, 05:01 PM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
March 22, 2012, 05:01 PM
shelzalee
[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.