I have been able to get the Oracle CONNECT BY to work by using an SQL passthrough but I was wondering if there is something similar in FOCUS?

Reporting from Financial hierarchies in FOCUS is somehow similar (?) to the Oracle CONNECT BY construct.

Below is the example. The CENTGL table used in the example is taken from the Century Corp. demo, and loaded into the Oracle database.

The ORA_CENTGL master file:

FILE=ORA_CENTGL, SUFFIX=SQLORA ,$
SEGNAME=ORA_CENTGL,
SEGTYPE=S0 ,$
FIELD=FOCLIST, FOCLIST, I11, I4, MISSING=ON ,$
FIELD=GLACCT, GLACCT, A7, A7, MISSING=ON ,$
FIELD=GLPAR, GLPAR, A7, A7,
PROPERTY=PARENT_OF, REFERENCE=GLACCT,
MISSING=ON ,$
FIELD=GLTYPE, GLTYPE, A1, A1, MISSING=ON ,$
FIELD=GLROLL, GLROLL, A1, A1, MISSING=ON ,$
FIELD=GLLEVEL, GLLEVEL, I11, I4, MISSING=ON ,$
FIELD=GLCAP, GLCAP, A30, A30,
PROPERTY=CAPTION, REFERENCE=GLACCT,
MISSING=ON ,$
FIELD=ALINE, ALINE, A6, A6, MISSING=ON ,$

The Oracle SQL query with CONNECT BY, which shows the accounts hierarchy:

SQL SQLORA
SELECT LPAD('-', LEVEL * 2, '-') || GLCAP AS GLCAP
FROM ORA_CENTGL
START WITH GLPAR IS NULL
CONNECT BY PRIOR GLACCT=GLPAR;
END

The FOCUS FML hierarchical report which shows the same accounts hierarchy:

SET BLANKINDENT=ON
TABLE FILE ORA_CENTGL
SUM COMPUTE DUMMY/A1 = ''; AS ''
FOR GLACCT
1000 WITH CHILDREN ALL AS CAPTION
END

You can find more information about the FOCUS FML syntax and features (including reporting from hierarchies) in the "WebFOCUS Creating Financial Reports Version 5 Release 2" manual.

Regards
Grzegorz