October 22, 2004, 03:07 PMreFOCUSing
Oracle CONNECT BY
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?
October 25, 2004, 09:40 AM<Grzegorz>
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 ,$
FIELD=FOCLIST, FOCLIST, I11, I4, MISSING=ON ,$
FIELD=GLACCT, GLACCT, A7, A7, MISSING=ON ,$
FIELD=GLPAR, GLPAR, A7, A7, PROPERTY=PARENT_OF, REFERENCE=GLACCT,
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,
FIELD=ALINE, ALINE, A6, A6, MISSING=ON ,$
The Oracle SQL query with CONNECT BY, which shows the accounts hierarchy:
SELECT LPAD('-', LEVEL * 2, '-') || GLCAP AS GLCAP
START WITH GLPAR IS NULL
CONNECT BY PRIOR GLACCT=GLPAR;
The FOCUS FML hierarchical report which shows the same accounts hierarchy:
TABLE FILE ORA_CENTGL
SUM COMPUTE DUMMY/A1 = ''; AS ''
1000 WITH CHILDREN ALL AS CAPTION
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.