Slightly off topic but want to add this here to help the next forum traveler. My business problem to solve is to return a comma separated concatenated list of values from a multivalue attribute on the project object. That FED_FLATTEN_MVLOOKUPS_FCT function is pretty worthless...
Thanks Senthi for the Oracle example using listagg. I'm MSSQL, so no listagg for us but this put me on the right path. The best solution I've found for MSSQL is the FOR XML clause. Code sample and results below.
SELECT
INV.NAME
, INV.CODE
, MVL_CONCAT.AUDIENCE
FROM INV_INVESTMENTS INV
LEFT JOIN (SELECT PK_ID , STUFF(( SELECT ', '+ (SELECT LOOKUP.NAME FROM CMN_LOOKUPS_V LOOKUP WHERE LOOKUP.LANGUAGE_CODE = 'en' AND LOOKUP.LOOKUP_CODE = MVL1.VALUE AND LOOKUP.LOOKUP_TYPE = 'OSUWMC_AUDIENCE')
FROM ODF_MULTI_VALUED_LOOKUPS MVL1
WHERE MVL2.PK_ID = MVL1.PK_ID FOR XML PATH('')),1 ,1, '') Audience
FROM ODF_MULTI_VALUED_LOOKUPS MVL2
WHERE MVL2.OBJECT = 'project'
AND MVL2.ATTRIBUTE = 'osuwmc_audience'
GROUP BY PK_ID) MVL_CONCAT ON MVL_CONCAT.PK_ID = INV.ID
WHERE MVL_CONCAT.AUDIENCE IS NOT NULL
Thanks again to all here who keep the lights on, contribute and make all this Community Forum stuff happen. **KAPOW!** Another solution... delivered.
Ideally, we simply need a function similar to FED_FLATTEN_MVLOOKUPS_FCT that returns the language appropriate NAME values.
Please vote up the Idea Database Function for Comma Separated MultiValue Lookup Name Results if you believe this would provide your organization value.
HTH the next traveler.
Rob