Livereport Inner join help

Firaz_Alyas

Hi,

 

Im trying to get Docs with most versions using the default livereport but editing it so that the parent folder os also listed. However my inner join is not working, can anyone advise?

 

select d.name, d.subtype, d2.name, DVersData.MimeType, DVersData.DataSize, count(*) "count(*)"
from dtree d, DVersData

Inner join dtree d2 on d.parentid = d2.dataid
 

where %2 and (d.DataID = DVersData.DocID)
group by d.name, d.subtype, d2.name, DVersData.MimeType, DVersData.DataSize
order by count(*) desc

 

 

Donna Nalls

Hello,

Not seeing an error message, i'm not sure that the inner join is your problem.  I do see that you are referencing .name in both d and d2, so that might be causing a problem.  Here is a query that might get you started:

-----

select DTree.Name, DTree.subtype, dt2.name as "ParentName",
 DVersData.MimeType, DVersData.DataSize
 from DTree, DTree dt2, DVersData
 where DTree.DataID=DVersData.DocID
 and %1
 and DTree.parentid = dt2.dataid
 order by DVersData.DataSize desc

-----

%1 is the permissions filter - i removed doc filter because you had subtype in your query.

I also think you may want to keep the original "DTree" instead of giving it an alias because the permissions code may be looking for a "DTree" reference (not sure, though).

Good luck!

Donna