Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Go
New
Search
Notify
Tools
Reply
  
Oracle CONNECT BY
 Login/Join
 
Guru
posted
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?
 
Posts: 406 | Location: Canada | Registered: May 31, 2004Reply With QuoteReport This Post
<Grzegorz>
posted
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
 
Reply With QuoteReport This Post
  Powered by Social Strata  
 


Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.