AnsweredAssumed Answered

Error in Query - attempting to pull rows from MVL results

Question asked by Cris-Rogers on Oct 9, 2009
Latest reply on Oct 9, 2009 by sundar
I'm attempting to write (poorly mind you) a query that will pull multi-valued lookup results from a   parent/child-object group.   (Short story - we have project specific rights applied to specific users through groups.   We want to see who has what groups assigned to them.)I'm getting a "ORA-00923: FROM keyword not found where expected" error when I attempt to preview the query below, and I'm not the most experienced SQL person (so say the very least)...  Can anyone shed light on how to present multi-valued lookup entries without the dreaded "duplicate dimensional data" error?Thank you,Cris  SELECT
@SELECT:DIM:USER_DEF:IMPLIED:projesr:ESRALIAS.PK_ID:MVLID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.VALUE:ResourceID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.LAST_UPDATED_DATE:MVL_LastUpdated@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.LAST_UPDATED_BY:MVL_LastUpdatedBy@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIASPWC_GROUP:GroupAssigned@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.ID:ESR_ID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.Last_Name:U_LastName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.First_Name:U_FirstName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.COMPANY_ID:CompanyIntID@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.COMPANY_NAME:CompanyName@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.COMPANY_ID:CompanyCode@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.CODE:EngagementCode@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:projesr:ESRALIAS.NAME:EngagementName@


FROM
(select DISTINCT
r:ODF_MULTI_VALUED_LOOKUPS.PK_ID,
ODF_MULTI_VALUED_LOOKUPS.VALUE,
ODF_MULTI_VALUED_LOOKUPS.LAST_UPDATED_DATE,
ODF_MULTI_VALUED_LOOKUPS.LAST_UPDATED_BY,
ODF_CA_PWC_ENGAGEMENT_RIGHT.PWC_GROUP,
ODF_CA_PWC_ENGAGEMENT_RIGHT.ID,
CMN_SEC_USERS.Last_Name,
CMN_SEC_USERS.FIRST_NAME,
CMN_SEC_USERS.COMPANY_ID,
SRM_COMPANIES.COMPANY_ID,
SRM_COMPANIES.COMPANY_NAME,
INV_INVESTMENTS.CODE,
INV_INVESTMENTS.NAME

FROM ODF_MULTI_VALUED_LOOKUPS
LEFT JOIN CMN_SEC_USERS On ODF_MULTI_VALUED_LOOKUPS.VALUE = CMN_SEC_USERS.ID
INNER JOIN ODF_CA_PWC_ENGAGEMENT_RIGHT
On ODF_MULTI_VALUED_LOOKUPS.PK_ID = ODF_CA_PWC_ENGAGEMENT_RIGHT.ID
LEFT JOIN INV_INVESTMENTS
On ODF_CA_PWC_ENGAGEMENT_RIGHT.ODF_PARENT_ID = INV_INVESTMENTS.ID
LEFT JOIN SRM_COMPANIES
On CMN_SEC_USERS.COMPANY_ID = SRM_COMPANIES.ID
WHERE ODF_MULTI_VALUED_LOOKUPS.OBJECT = 'pwc_engagement_right')ESRALIAS
WHERE @FILTER@

Outcomes