DQL Query Help

Prasad_1231

Give records for "XYZ" cabinet as per below condition

1) wihout versions

2) without renditions

3) wtih linked

4) dm_document all attributes

5) with folder path

Thanks, Prasad

vincentklock

Hi Prasad,

You can try something like this (checked against 6.7SP1 repository):

SELECT doc.*, fldr.r_folder_path
FROM dm_document doc, dm_folder fldr
WHERE ANY doc.i_folder_id = fldr.r_object_id
AND fldr.r_folder_path LIKE '/Cabinet/Parent_folder/%'

This will give you all the attributes of the documents under the /Cabinet/Parent_folder folder.

For your other criteria:

1. you can add the following criterion: r_object_id = i_chronicle_id --> by definition will give you only unversionned documents, and will use good indexes.

2. you can use r_content_size = 0 if you mean "no content". Else, you will have to add the following join:

dmr_content cnt
WHERE ANY cnt.parent_id = doc.r_object_id AND cnt.rendition NOT IN (0)

3. I don't understand what you mean by "with linked"

4. referred to as "doc.*" above

5. see above.

I have checked and tested against a 6.7 repository. I am pretty sure it will not work on 5.3 ("your query is selecting repeating attributes and joining types" error may be raised), I cannot tell for 6.0 and 6.5.

On 5.3 I would go with using the underlying database tables to achieve the same with repeating attributes: dm_dbo.dm_folder_r, dm_dbo.dmr_content_r and dm_dbo.dmr_content_s.