AnsweredAssumed Answered

How to see the option of a field in the object Project when the field is in the table ODF_MULTI_VALUED_LOOKUPS 

Question asked by Patricio_1979 on Aug 3, 2018
Latest reply on Aug 6, 2018 by nick_darlington

Hello,

 

I'm contact with the CA PPM comunity because I have a problem to show in a sql server query the options that contain the field "Áreas de Demanda Cruzada" (ID: osde_areas_dc) that is in the table "ODF_MULTI_VALUED_LOOKUPS"

If anybody can help with the join between this table and the projects table I will be grateful.

 

 

 

Best Regards.
Patricio.

 

 

 

Thans a lot for your help, the query looks like: 

 

SELECT DISTINCT
t1.CODE,
(CASE WHEN ic.osde_riesgoscriticos = '0' THEN 'No' ELSE 'Sí' END) as TieneRiesgosCriticos,
(CASE WHEN ic.osde_problemasdc = '0' THEN 'No' ELSE 'Sí' END) as TieneProblemasConDC,
ic.osde_breveexplic BreveExplicación,

STUFF((SELECT DISTINCT ' - ' + demo_mvl_nls.name
FROM odf_multi_valued_lookups t2
LEFT JOIN cmn_lookups demo_mvl_lkp
ON t2.value = demo_mvl_lkp.lookup_code AND demo_mvl_lkp.lookup_type = 'OSDE_OBS_EJECUCION'
LEFT JOIN cmn_captions_nls demo_mvl_nls
ON demo_mvl_lkp.id = demo_mvl_nls.pk_id AND demo_mvl_nls.table_name = 'CMN_LOOKUPS' and demo_mvl_nls.language_code = 'en'
WHERE t1.id = t2.pk_id AND t2.object = 'project' AND t2.attribute = 'osde_areas_dc'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,0,'') AreasDemandaCruzada,

STUFF((SELECT DISTINCT ' - ' + demo_mvl_nls.name
FROM odf_multi_valued_lookups t2
LEFT JOIN cmn_lookups demo_mvl_lkp
ON t2.value = demo_mvl_lkp.lookup_code AND demo_mvl_lkp.lookup_type = 'OSDE_OBS_SOLICITANTE'
LEFT JOIN cmn_captions_nls demo_mvl_nls
ON demo_mvl_lkp.id = demo_mvl_nls.pk_id AND demo_mvl_nls.table_name = 'CMN_LOOKUPS' and demo_mvl_nls.language_code = 'en'
WHERE t1.id = t2.pk_id AND t2.object = 'project' AND t2.attribute = 'osde_impactointerno'
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,0,'') ImpactoInterno

FROM INV_INVESTMENTS t1

INNER JOIN odf_ca_inv ic ON t1.id = ic.id
INNER JOIN inv_projects p ON t1.id = p.prID
INNER JOIN odf_ca_project pc ON t1.id = pc.id and pc.partition_code = 'osde_binario'


WHERE t1.is_active = 1
AND t1.CODE like '%GDM%'

 

Best Regards

Patricio.

Outcomes