Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS
 Login/Join
 
Member
posted
Hello everyone,
I'm debugging a report that gives me the following error:

"(FOC030) ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS"

After some research, I found out that this particular error happens when there is a problem with an existing JOIN.
Below is some of the report's code.

 JOIN LEFT_OUTER AAI IN V TO AAI IN HAI AS JOIN8
END

TABLE FILE V
PRINT
F_NAME
L_NAME
SCC
SCN
ST
SD
AAI
BY AAI
ON TABLE PCHOLD FORMAT EXL2K
END 


NOTE: V and HAI are both hold files created prior to this block of code.
Any ideas?

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


WebFOCUS Developer Studio 8104 / Windows 7 / HTML and Excel
 
Posts: 16 | Registered: August 11, 2015Report This Post
Expert
posted Hide Post
There are a couple of solutions to this.

The simplest one is to just PRINT all the fields, no BY fields.

I would also use TAGs with you join, to identify the fields specifically.

e.g.

JOIN LEFT_OUTER AAI IN V TAG A TO AAI IN HAI TAG B AS JOIN8

TABLE FILE V
PRINT
F_NAME
L_NAME
SCC
SCN
ST
SD
A.AAI
BY A.AAI
ON TABLE PCHOLD FORMAT EXL2K
END 


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

 
Posts: 6347 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Report This Post
Virtuoso
posted Hide Post
Ali,
Are both V and HAI sequential HOLD files?
If so, are they both sorted by AAI? and why LEFT_OUTER?


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
Waz,
I commented the BY field and nothing changed, I still get the error. Tagging didn't do much.

Danny,
Actually, I didn't develop this report so I have no idea why they used LEFT_OUTER JOIN.

V is a Hold file the gets generated from a SQLMSS query.
Below is the code for the two HOLD files.


 JOIN AP.AP.AAI IN AP TO IA.IA.F1 IN IA TAG JOIN6 AS JOIN6 END

TABLE FILE AP
PRINT 
AAI
F_NAME
L_NAME
BY AAI NOPRINT
ON TABLE HOLD AS HAI
END

TABLE FILE HAI
PRINT
AAI
BY AAI NOPRINT
ON TABLE HOLD AS HAI
END


SQL SQLMSS
SELECT F1
F2
F3
FROM [TABLE]
WHERE [CONDITION]

TABLE FILE SQLOUT
PRINT
F1
F2
BY F1 NOPRINT
ON TABLE HOLD AS V
END

JOIN F1 IN V TO ALL S1 IN SS AS JOIN1
JOIN F1 IN V TO F1 IN IA AS JOIN2
JOIN F1 IN V TO SPI IN SEE AS JOIN 3
JOIN F1 IN V TO ALL AA IN AP AS JOIN4
END

TABLE FILE V
PRINT 
F1
F_NAME
L_NAME
SCC
SCN
ST
SD
AAI
WHERE ST IS NOT MISSING
BY AAI
WHERE [CONDITION]
ON TABLE HOLD AS V FORMAT FOCUS INDEX ID
END 

JOIN LEFT_OUTER AAI IN V TO AAI IN HAI AS JOIN8
END

TABLE FILE V
PRINT
F_NAME
L_NAME
SCC
SCN
ST
SD
AAI
BY AAI
ON TABLE PCHOLD FORMAT EXL2K
END  


WebFOCUS Developer Studio 8104 / Windows 7 / HTML and Excel
 
Posts: 16 | Registered: August 11, 2015Report This Post
Virtuoso
posted Hide Post
Ali,
1.
Do you know for which TABLE request WF issues the FOC030 error message?
If you don't, I suggest inserting -RUN after each and every END.

2.
I don't see why you should have:
quote:

TABLE FILE AP
PRINT
AAI
F_NAME
L_NAME
BY AAI NOPRINT
ON TABLE HOLD AS HAI
END

TABLE FILE HAI
PRINT
AAI
BY AAI NOPRINT
ON TABLE HOLD AS HAI
END

The second TABLE is superfluous.

3.
Why the last JOIN?
quote:

JOIN LEFT_OUTER AAI IN V TO AAI IN HAI AS JOIN8
END


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
I suggest you clean up the code a bit and try again.

1. Your first JOIN, a one-liner, concludes with END. END (when present) should be on a line by itself, same as with TABLE or DEFINE.

2. You have requests where the Hold file has the same name as the source file (HAI once, and V twice). Keep all the filenames distinct.

3. Some of those reused filenames are involved in JOINs. I'm not sure a priori which master and which data will be used at run-time in the subsequent TABLE requests; play it safe and avoid the ambiguity.

4. "JOIN F1 IN V TO SPI IN SEE AS JOIN 3" -- note the space before "3"

5. It's best practice to CLEAR joins as soon as they are no longer needed.

In particular...
quote:
JOIN F1 IN V TO ALL S1 IN SS AS JOIN1
JOIN F1 IN V TO F1 IN IA AS JOIN2
JOIN F1 IN V TO SPI IN SEE AS JOIN 3
JOIN F1 IN V TO ALL AA IN AP AS JOIN4
END

TABLE FILE V
. . .
ON TABLE HOLD AS V FORMAT FOCUS INDEX ID
END

JOIN LEFT_OUTER AAI IN V TO AAI IN HAI AS JOIN8
END

TABLE FILE V
. . .
ON TABLE PCHOLD FORMAT EXL2K
END

... the interpreter will see five JOINs (from "V" to other sources) in effect, four of which were issued against a different "V", so there's ample opportunity for confusion.

You may want to throw in
??F filename
CHECK FILE filename PICT
to see exactly what logical structure that TABLE is dealing with. I'd do that with the operative code as-is, and again after modification, so you can witness the effect of your changes.

Let us know how it plays out.


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Jack beat me to the chase. Your code needs to be cleaned up in several places.


8.2.02M (production), 8.2.02M (test), Windows 10, all outputs.
 
Posts: 1113 | Location: USA | Registered: January 27, 2015Report This Post
Member
posted Hide Post
Danny,
The last join is needed for "PN". My mistake, I didn't include it in the first post.
I removed the second HAI table and I was getting the same error. I get (FOC030) error on the table before the last join. It always points to line number where "END" before the last join is coded.

j.gross,
Thanks for suggesting cleaning up the code. It does help when your code is nice and neat. I implemented some of the changes you suggested. For the debugging step where do you mean to throw the "??F filename CHECK FILE filename PICT"?

JOIN AP.AP.AAI IN AP TO IA.IA.F1 IN IA TAG JOIN6 AS JOIN6
END

TABLE FILE AP
PRINT 
AAI
PN
F_NAME
L_NAME
BY AAI NOPRINT
ON TABLE HOLD AS HAI
END


TABLE FILE HAI
PRINT
AAI
PN
BY AAI NOPRINT
ON TABLE HOLD AS HAI
END


SQL SQLMSS
SELECT F1,
F2,
F3,
F4,
F5,
F6,
F7
FROM [TABLE]
WHERE [CONDITION]

TABLE FILE SQLOUT
PRINT
F1
F3
BY F1 NOPRINT
ON TABLE HOLD AS V
END

JOIN F1 IN V TO ALL S1 IN SS AS JOIN1
JOIN F1 IN V TO F1 IN IA AS JOIN2
JOIN F1 IN V TO SPI IN SEE AS JOIN3
JOIN F1 IN V TO ALL AA IN AP AS JOIN4
END

TABLE FILE V
PRINT 
F1
F_NAME
L_NAME
SCC
SCN
ST
SD
AAI
WHERE ST IS NOT MISSING
BY AAI
WHERE [CONDITION]
ON TABLE HOLD AS V FORMAT FOCUS INDEX ID
END 

JOIN LEFT_OUTER AAI IN V TO AAI IN HAI AS JOIN8
END

TABLE FILE V
PRINT
F_NAME
L_NAME
SCC
SCN
ST
SD
AAI
HAI.PN
BY AAI
ON TABLE PCHOLD FORMAT EXL2K
END   

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


WebFOCUS Developer Studio 8104 / Windows 7 / HTML and Excel
 
Posts: 16 | Registered: August 11, 2015Report This Post
Virtuoso
posted Hide Post
Ali-
Here's what I suggest you do.
My changes to operative code are in lower-case.
JOIN AP.AP.AAI IN AP TO IA.IA.F1 IN IA TAG JOIN6 AS JOIN6
END
??f ap
-run

TABLE FILE AP
PRINT
AAI
PN
F_NAME
L_NAME
BY AAI NOPRINT
ON TABLE HOLD AS HAI format focus index aai
END

join clear *
-run

SQL SQLMSS
SELECT F1,
F2,
F3,
F4,
F5,
F6,
F7
FROM [TABLE]
WHERE [CONDITION]

TABLE FILE SQLOUT
PRINT
F1
F3
BY F1 NOPRINT
ON TABLE HOLD AS V
END
-run

join clear *

JOIN F1 IN V TO ALL S1 IN SS AS JOIN1
JOIN F1 IN V TO     F1 IN IA AS JOIN2
JOIN F1 IN V TO     SPI IN SEE AS JOIN3
JOIN F1 IN V TO ALL AA IN AP AS JOIN4
END
??f v
-run

TABLE FILE V
PRINT
F1
F_NAME
L_NAME
SCC
SCN
ST
SD
BY AAI
where ST IS NOT MISSING
WHERE [CONDITION]
ON TABLE HOLD AS v2
-* FORMAT FOCUS not needed for the Join and Table below.
-* INDEX ID  <== ID is not listed as a column of the request.
-* it's also not referenced later in the code provided.
END

join clear *

JOIN LEFT_OUTER AAI IN v2 TO AAI IN HAI AS JOIN8
END
?ff v2
check file v2 pict
-run

TABLE FILE v2
PRINT
F_NAME
L_NAME
SCC
SCN
ST
SD
AAI
HAI.PN
BY AAI
ON TABLE PCHOLD FORMAT EXL2K
END


- Jack Gross
WF through 8.1.05
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Virtuoso
posted Hide Post
Jack,
May I suggest:
1. Not use the extraneous END after the one line JOINs.
2. In some places you wrote ??F instead of ?FF. This will confuse Ali.

Aside from that, yes, cleaning up is good practice!


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Virtuoso
posted Hide Post
Dan: Thanks for the correction.

Ali: Adding CHECK FILE V just before the final TABLE FILE V would force WF to re-read and parse the synonyms involved (for the host file and all that are joined to it) at that point. If the FOC030 is caused by hold-overs from earlier parsing (for the same filename) that has been rendered invalid by subsequent code (e.g., re-holding to the same filename), that may clear the FOC030.

Of course that's a rather fragile solution -- the next one to touch the code may presume the CHECK FILE is extraneous scaffolding, delete it, and fall into the same hole you did. Better to first clean up the program, and then address the problem on a clean drawing board if it persists.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Report This Post
Member
posted Hide Post
Jack and Danny,
Thank you for your help.
The report is working now and what I did is replaced the SQLMSS portion of the code with normal FOCUS code and sorted by AAI.
I did implement your suggestions and the code looks better now.

Marking this discussion as [SOLVED]

Ali


WebFOCUS Developer Studio 8104 / Windows 7 / HTML and Excel
 
Posts: 16 | Registered: August 11, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] ALL VERB OBJECTS MUST BE IN THE SAME PATH AS THEIR SORT FIELDS

Copyright © 1996-2020 Information Builders