AnsweredAssumed Answered

How to Return Current Rows From Multi_Valued_Lookups

Question asked by cinghiale on Dec 19, 2011
Latest reply on Dec 21, 2011 by cinghiale
I used a query from this thread to get Business Unit. When I compare my results to Clarity UI, I get 5 more Business Units from my query than from the UI. The delta appear to be older values that have been superceded. How can I get back just the current values?

SELECT INV.NAME "Project Name"
, INV.ID
, UNIT.NAME as OBS_UNIT
, MVL.ATTRIBUTE
, INV.CODE
, MVL.VALUE
, LOOKUP.NAME "Business Unit"
, OBS.UNIT_ID

FROM INV_INVESTMENTS INV
INNER JOIN ODF_MULTI_VALUED_LOOKUPS MVL ON INV.ID = MVL.PK_ID
INNER JOIN PRJ_OBS_ASSOCIATIONS OBS ON INV.ID = OBS.RECORD_ID
INNER JOIN prj_obs_units UNIT ON UNIT.ID = OBS.UNIT_ID
INNER JOIN
(SELECT LOOKUP_CODE, NAME , MAX(last_updated_date)
FROM CMN_LOOKUPS_V WHERE LANGUAGE_CODE = 'en' AND IS_ACTIVE = 1
GROUP BY LOOKUP_CODE, NAME
) LOOKUP ON MVL.VALUE= LOOKUP.LOOKUP_CODE

WHERE INV.ODF_OBJECT_CODE IN ('project')
AND INV.CODE = 'P02777'
AND MVL.ATTRIBUTE = 'bus_unit'
AND UNIT.type_id = 5000017
AND OBS.TABLE_NAME = 'SRM_PROJECTS'

Outcomes