Retrieving both the EFS path & EWS path in one report

Colin_Schmidt

The EFS path is in the providerdata.providerdata. The EWS path can be obtained from DTREE via the ORACLE function "SYS_CONNECT_BY_PATH" with the following code: select dataid, LEVEL, 'Enterprise' || SYS_CONNECT_BY_PATH (name, ' | ') " EWS Path" from livelink.dtreeCONNECT BY PRIOR dataid=parentid START WITH (name='Corporate Base' AND parentid=2000) I'd like top pull both together into one report. The DVERSDATA table is about the only table that can link DTREE to PROVIDERDATA. In either case, a join of some kind or a sub-query needs to be executed in someway and that combination "has left the building" as far as I can figure out.Other information in DTREE and DVERSDATA I'd like to pull out too: RESERVED, DELETED, DATASIZE, VERSION. I'm able to produce two different reports with DTREE & DVERSDATA and PROVIDERDATA & DVERSDATA, but not with all three.

John W. Simon, Jr.

You might try a search for SYS_CONNECT_BY_PATH in the discussions for other comments. My guess is your problem is you can not connect by and join in the same statement. You will need to rework your query to be something like...select a.dataid, a.path, b.datasize, c.providerdatafrom (select ... from dtree connect by...) a,dversdata b,providerdata cwhere a.dataid = b.docid and b.providerid = c.provideridand maybe include (and a.versionnum = b.version) if you just want to limit to the current version of the document.Hope that helps. Cheers...