- Seen many ways to filter based on OBS
- Many rely on complex logic, left joins to inline views, or multiple sub-queries.
- Using EXISTS and OBS_UNITS_FLAT_BY_MODE table provides an easy solution.
- filter by unit only, unit and descendents, or units or ancestors
SELECT SRMR.FULL_NAME FROM SRM_RESOURCES SRMR WHERE (OBS_ID IS NULL OR EXISTS
(SELECT 1 FROM OBS_UNITS_FLAT_BY_MODE OBSM
JOIN PRJ_OBS_ASSOCIATIONS OBSA
ON OBSM.LINKED_UNIT_ID= OBSA.UNIT_ID AND OBSA.TABLE_NAME="SRM_RESOURCES"
WHERE OBSM.UNIT_ID=OBS_ID
AND OBSM.UNIT_MODE=NVL(OBS_MODE,'OBS_UNIT_AND_CHILDREN')
AND OBSA.RECORD_ID=SRMR_ID))
This will give the good performance than PRJ_OBS tables.
