Clarity

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.

I'll use a demo attribute I created on project that is a multi-valued lookup on the Bill Expense Type.  I'll also not use any built-in views so you can see the underlying joins in full; though using the _V views may save some of this effort for you normally.

Then if we say I have a project with these values on:

I can create a query like the following to retrieve the data:

select i.code, demo_mvl.value, demo_mvl_nls.name
from inv_investments i
left join odf_multi_valued_lookups demo_mvl
    on i.id =  demo_mvl.pk_id and demo_mvl.object = 'project' and demo_mvl.attribute = 'demo_mvl'
left join cmn_lookups demo_mvl_lkp
    on demo_mvl.value = demo_mvl_lkp.lookup_code and demo_mvl_lkp.lookup_type = 'BILL_EXPENSE_TYPE'
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 i.code = 'P1'

Note that the results return multiple records for a single project, because of the nature of the multiple stored values, but it gets the name value you were looking for:

Then if you wanted to collapse them to a single line, you would need to use a DB function like Oracle's listagg:

select i.code
    , listagg(demo_mvl_nls.name, ';') within group (ORDER BY i.code) as demo_mvl
from inv_investments i
left join odf_multi_valued_lookups demo_mvl
    on i.id =  demo_mvl.pk_id and demo_mvl.object = 'project' and demo_mvl.attribute = 'demo_mvl'
left join cmn_lookups demo_mvl_lkp
    on demo_mvl.value = demo_mvl_lkp.lookup_code and demo_mvl_lkp.lookup_type = 'BILL_EXPENSE_TYPE'
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 i.code = 'P1'
group by i.code

To net a result like this, which is like my project list view output:

SQL Server doesn't bring in a similar function of its own until STRING_AGG in SQL Server 2017:

https://docs.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017

However, if you need one and scavenge around, you will find alternative solutions people have used for this.  There are threads on stack overflow and other places offering alternatives to listagg() that can be used in MSSQL.

Edits: Had to update multiple times to get the images included.

Thaks a lot Nick, very didactic the example. At this moment I try to traduce this ORACLE function to SQL.

For SQL the most common method I have seen used (and have used myself before) is by using the FOR XML PATH pattern with or without other functions like STUFF() to help with the presentation part.

Some references/examples:

https://www.sqlhammer.com/transact-sql-string_agg/

https://stackoverflow.com/questions/15477743/listagg-in-sqlserver