An example reporting query for a Microsoft SQL Server database is as follows:
Example
select
e1.CLARITY_NODE_OBJECT_ID,
e1.CLARITY_OBJECT_NAME,
e1.CLARITY_DOMAIN,
TO_CHAR(e1.UPLOAD_TIME, 'YYYY-MM-DD"T"HH24:MI:SS') UPLOAD_TIME,
TO_CHAR(e1.COLLECTION_TIME, 'YYYY-MM-DD"T"HH24:MI:SS') COLLECTION_TIME,
e1.AGENT_HOSTNAME,
i1.*,
custom_fields.*
from
common_elements e1
join common_mssql_db i1
on e1.collection_id = i1.collection_id, common_custom_fields custom_fields
join common_elements custom_commons
on custom_fields.collection_id = custom_commons.collection_id
where
e1.clarity_object_id = custom_commons.clarity_object_id
and
e1.clarity_object_id = __ENTITY_ID__
and
e1.clarity_manager_id = '__MGR_ID__'
and
e1.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
common_mssql_db p2
join common_elements e2
on p2.collection_id = e2.collection_id
where
e2.clarity_object_id = __ENTITY_ID__
and
e2.clarity_manager_id = '__MGR_ID__'
and
e2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)
and
custom_commons.collection_time = (
select
/*+ INDEX_DESC(e2 IX_COMMON_ELEMENTS_TIME) */ collection_time
from
common_custom_fields custom_fields2
join common_elements custom_commons2
on custom_commons2.collection_id = custom_fields2.collection_id
where
custom_commons2.clarity_object_id = __ENTITY_ID__
and
custom_commons2.clarity_manager_id = '__MGR_ID__'
and
custom_commons2.collection_time <= to_date('__COLLECTION_TIME__', 'YYYY-MM-DD HH24:MI:SS')
and
rownum = 1
)