What's the Oracle statement for grouping documents based on attribute which only exist on workspace?

Ludwig_Stokkelaar

Hi

 

I need a SQL statement which provides an overview of all documents into project workspace grouped by a certain ProjectCat attribute which only exists on projectworkspace self and not on the documents inside somehwhere in the workspaces. I have a query which works in SQL Server, but doens't work on Oracle. Anybody has a tip how to convert this one ?

 

Tips highly appreciated

Ludwig

 

 

WITH Parent AS
(
SELECT ParentID, DataID, CONVERT(nvarchar (256 ), Name ) AS QName , Subtype, a.ValStr "ProjectCat"
FROM DTree "t", llattrdata "a"
WHERE (t.DataID=a.ID and a.DefID = 2458659 and a.AttrID = 17 and t.VersionNum = a.VerNum )
AND Subtype = 202
UNION ALL

SELECT DT.ParentID,
DT.DataID,
CONVERT(nvarchar (256), Name ) AS QName,
DT.Subtype,
Parent.ProjectCat
FROM DTree DT
INNER JOIN Parent ON Parent.DataID = DT.ParentID
)

SELECT Parent.ProjectCat, COUNT(*) "Number of documents"
FROM parent
WHERE SubType = 144
GROUP BY ProjectCat

Colin_Schmidt

Does this come close?  Setting the Drop down to choose a Project....

select lla.valstr "ProjectCat", dt.subtype, count(*)

from dtree dtp  

  join llattrdata lla on dtp.dataid = lla.id and lla.defid = 2458659 and lla.attrid = 17 and dtp.versionnum = lla.vernum 

  join dtree dt on dtp.dataid = dt.ownerid and dt.subtype = 144 

where dtp.subtype = 202 and dtp.dataid = %1  

Group by lla.valstr, dt.subtype

Colin J. 

On Thu, Feb 19, 2015 at 12:20 PM, eLink Entry: Content Server LiveReports Forum <livereportsdiscussion@elinkkc.opentext.com> wrote:

What's the Oracle statement for grouping documents based on attribute which only exist on workspace?

Posted byludwig.stokkelaar@delawareconsulting.com (Stokkelaar, Ludwig)On 02/19/2015 12:15 PM

Hi I need a SQL statement which provides an overview of all documents into project workspace grouped by a certain ProjectCat attribute which only exists on projectworkspace self and not on the documents inside somehwhere in the workspaces. I have a query which works in SQL Server, but doens't work on Oracle. Anybody has a tip how to convert this one ? Tips highly appreciatedLudwig  WITH Parent AS ( SELECT ParentID, DataID, CONVERT(nvarchar (256 ), Name ) AS QName , Subtype, a.ValStr "ProjectCat" FROM DTree "t", llattrdata "a" WHERE (t.DataID=a.ID and a.DefID = 2458659 and a.AttrID = 17 and t.VersionNum = a.VerNum ) AND Subtype = 202 UNION ALL SELECT DT.ParentID, DT.DataID, CONVERT(nvarchar (256), Name ) AS QName, DT.Subtype, Parent.ProjectCat FROM DTree DT INNER JOIN Parent ON Parent.DataID = DT.ParentID ) SELECT Parent.ProjectCat, COUNT(*) "Number of documents" FROM parent WHERE SubType = 144 GROUP BY ProjectCat

---

[To post a comment, use the normal reply function]
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center

This electronic message contains information from MeadWestvaco Corporation (mwv.com) or subsidiary companies, which may be confidential, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipient(s) named. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify MeadWestvaco immediately at postmaster@mwv.com.