| query [message #403201] |
Thu, 14 May 2009 03:05  |
manijash
Messages: 2
Registered: May 2009
Location:
|
Junior Member |
|
|
Pl. suggest how to rewrite the below query in better way.
SELECT sds.obj_id, sds.seq_no, ods.parent_obj_id, ods.viewable,
boj.obj_created, boj.obj_modified, boj.obj_name, boj.obj_owner,
boj.obj_owner_name, boj.obj_type, boj.obj_descr,
sds.QUERY AS QUERY, sds.content_set_name, sds.collections,
sds.search_type, sds.srch_folder_id, dds.sort_fields,
dds.retrieve_fields, dds.sort_orders, dds.content_category,
dds.name_value, dds.offset, dds.size_count, oda.alert_id,
sds.FIELDS, sds.field_values AS field_values
FROM display_details dds,
search_details sds,
base_object boj,
object_def_alert oda,
object_def_search ods
WHERE sds.obj_id = boj.obj_id
AND oda.alert_ref_id(+) = sds.obj_id
AND dds.obj_id(+) = sds.obj_id
AND ods.obj_id = sds.obj_id
AND sds.obj_id IN (323)
CONNECT BY PRIOR ods.obj_id = ods.parent_obj_id
ORDER BY sds.obj_id DESC, dds.content_category
|
|
|
|
|
|
| Re: query [message #403248 is a reply to message #403202] |
Thu, 14 May 2009 07:48  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Your CONNECT BY doesn't have a START WITH clause. That means you will generate a sub-tree starting with every single row in object_def_search.
You sure that's what you want?
Also, you create a tree by traversing from PARENT_OBJ_ID to OBJ_ID and then filter the query by just one of those OBJ_ID values, thereby undoing all of the work of traversing the tree. Doesn't really make much sense.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
