Handy DQL: Listing Folder Paths for Objects

It is a common need to print folder paths of sysobjects (documents are also sysobjects) as a part of DQL query results. The ROW_BASED hint makes it convenient to do so as shown in the query below.

SELECT s.object_name, f.r_folder_path
FROM dm_sysobject s, dm_folder f
WHERE s.i_folder_id = f.r_object_id
AND f.r_folder_path != ' '
AND additional conditions
ENABLE (ROW_BASED)

Note that you can replace dm_sysobject with another custom type, which is a descendant (direct or indirect subtype) of dm_sysobject. Don’t forget to add conditions for the where clause lest you should retrieve a large number of objects (unless that’s what you need).

Advertisements

2 thoughts on “Handy DQL: Listing Folder Paths for Objects

    1. The whole point of this post is to show how to join on repeating attributes using the ROW_BASED hint. Try it – it will open a world of possibilities that were restricted by the limitation you describe.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s