bhulett

Appending OBS Info to Resources or Projects

Discussion created by bhulett on Jul 6, 2010
Latest reply on Jul 7, 2010 by Chris_Hackett
For Clarity installs with Oracle back ends, here is a simple way to create views for resources or projects that append OBS ID, name, level, and path data. The following code provides this information real-time with good performance and can be adjusted to other types of OBS constructs.

Create the following base view. CREATE OR REPLACE VIEW OBS_PATH_LOOKUP_V
AS
select obs.ID,
obs.NAME,
obs.DEPTH,
substr((sys_connect_by_path (obs.NAME , '\')), 2, length(sys_connect_by_path (obs.NAME , '\')))
-- Do not use the \ char in an OBS name or the Oracle sys_connect_by_path will fail
from PRJ_OBS_UNITS obs
start with obs.ID = 5000001 -- OBS the view is based on
connect by prior obs.ID = obs.PARENT_ID and obs.TYPE_ID = 5000001;

Once view OBS_PATH_LOOKUP_V is created, create the following resource view. CREATE OR REPLACE VIEW SRM_RESOURCES_V
AS
SELECT r.*,
obsp.OBS_ID,
obsp.OBS_NAME,
obsp.OBS_DEPTH,
obsp.OBS_PATH
FROM SRM_RESOURCES r
LEFT OUTER JOIN (select a.RECORD_ID REC_ID, a.UNIT_ID OBS_ID
from prj_obs_associations a, prj_obs_units u
where a.TABLE_NAME = 'SRM_RESOURCES' and a.UNIT_ID = u.id and u.TYPE_ID = 5000001) obs on r.id = obs.rec_id
LEFT OUTER JOIN OBS_PATH_LOOKUP_V obsp on obs.obs_id = obsp.OBS_ID;

Here's the view for projects. CREATE OR REPLACE VIEW INV_PROJECTS_V
AS
select p.*,
obsp.OBS_ID,
obsp.OBS_NAME,
obsp.OBS_DEPTH,
obsp.OBS_PATH
FROM INV_PROJECTS p
LEFT OUTER JOIN (select a.RECORD_ID REC_ID, a.UNIT_ID OBS_ID
from prj_obs_associations a, prj_obs_units u
where a.TABLE_NAME = 'SRM_PROJECTS'
and a.UNIT_ID = u.id
and u.TYPE_ID = 5000001) obs on p.prid = obs.rec_id
LEFT OUTER JOIN OBS_PATH_LOOKUP_V obsp on obs.obs_id = obsp.OBS_ID;

Outcomes