Finding documents not having a PDF rendition

Hi,

I'm trying to identify documents in my docbase which don't have a PDF rendition, i.e. document objects which are not the parent of any content object with a PDF format. I know that i have such documents, as I can see examples in Webtop.

Unfortunately, the query below keeps returning 0 rows.

select * from dm_document(all) where r_object_id not in (select distinct parent_id from dmr_content where full_format = 'pdf')

Any idea what's wrong?

Thanks!

Raph

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

lastnitescurry

Get documents with pdf renditions

http://www.dctmcontent.com/Lists/DQL/DispForm.aspx?ID=42&ContentTypeId=0x0100D94FEDC8CA5B554887150EA97B552C91

from

http://www.dctmcontent.com/Lists/DQL/Category.aspx

All comments

yamangoyal

For those objects which you can see that PDF rendition is not available, can you put that screenshot and check the result of below dql:

select * from dmr_content where parent_id='?' // put ur object id from webtop

lastnitescurry

Get documents with pdf renditions

http://www.dctmcontent.com/Lists/DQL/DispForm.aspx?ID=42&ContentTypeId=0x0100D94FEDC8CA5B554887150EA97B552C91

from

http://www.dctmcontent.com/Lists/DQL/Category.aspx

aflowers001

First off make sure you have enough rights to execute the query correctly, i.e. try it as a super user.

Second, check the values you would want to use for full_format as per

select distinct full_format from dmr_content

mrgou

Interestingly, I do get results using the query Brian pointed at:

select * from gmp_document where not exists (select * from dmr_content where any parent_id=gmp_document.r_object_id and full_format='pdf')

I was told before that the "not in" clause can return unpredicable results. I guess that's another example.

acarver

I use the following query to list documents that do NOT have a PDF rendition:

SELECT r_object_id,object_name FROM dm_document c

WHERE FOLDER('/dmadmin')

AND

NOT EXISTS

(SELECT t.r_object_id FROM dmr_content t WHERE ANY parent_id_i = c.r_object_id_i AND full_format = 'pdf' ) ;

( I used to use a some nested sub-queries with the two tables but the above query is much faster ... I think the parent_id_i is the key improvement )