DQL to list number of empty folders grouped by cabinets

mrgou

Hi,

I'm reviewing empty folders in my docbase, which I simply do with:

select count(r_object_id) from dm_folder where r_link_cnt=0

Plain and simple.

Now, what would be even better would be to group that count by cabinet so I can see how many empty folders each cabinet contains, e.g.:

573,cabinet1

134,cabinet2

241,cabinet3

...

Any idea how to do that?

Thanks!

Raph

Elchin_Mahmudov

Hi,

Try this query

select count(f.r_object_id),c.object_name from dm_cabinet c,dm_folder f where f.r_link_cnt=0  and c.r_object_id=f.i_cabinet_id  group by c.object_name order by object_name

When the cabinet is empty the value of count(f.r_object_id) will be '1' because dm_cabinet is subtype of dm_folder

If you want only cabinets which is not empty try this

select count(f.r_object_id),c.object_name from dm_cabinet c,dm_folder f where f.r_link_cnt=0  and c.r_object_id=f.i_cabinet_id  group by c.object_name having count(f.r_object_id)>1 order by object_name

Hope it helps

Regards,

Elchin