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,

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 

Ali,
Are both V and HAI sequential HOLD files?
If so, are they both sorted by AAI? and why LEFT_OUTER?

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  

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

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 beat me to the chase. Your code needs to be cleaned up in several places.

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,

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,
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!

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.

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