Clarity

Hi, I'm trying to create a custom grid portlet with some of my customer's custom attributes and I have a few multi value lookups.

I was reading the different posts I was able to find here and I managed to create this query:

[color=#ff0707][size=6]SELECT DISTINCT port.name "Cartera", INV.NAME "Nombre de Proyecto" , LOOKUP.NAME "Gerencia Solicitante",
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) "Gestor de Proyecto"
FROM niku.INV_INVESTMENTS INV , niku.ODF_MULTI_VALUED_LOOKUPS MUL , niku.PMA_PORTFOLIO_CONTENTS CONT, niku.PMA_PORTFOLIOS PORT,
(SELECT LOOKUP_CODE , NAME FROM niku.CMN_LOOKUPS_V WHERE LOOKUP_TYPE = 'MACGERENCIASOLICITANTE') LOOKUP
WHERE INV.ID=MUL.PK_ID AND MUL.VALUE=LOOKUP.LOOKUP_CODE[size][color]


This query brings me the Portolio Name, The project Name, The Project Manager and a custom Multi Value Lookup called "Gerencia Solicitante". This so far is working ok.

My big issue since I'm not good with SQL is now how can I modify this query now to add other Multi Value Lookups. Can you please give me an idea on this? I need to add a new one like this one but [color=#fa0000][size=6]WHERE LOOKUP_TYPE = 'OBJ_PROJECT_STAKEHOLDER'[size][color]

Can you provide any idea on how should I modify my existing query to add this new Multi Value Lookup?

Thanks,

Juan

Not writing the entire query, and just using INV_INVESTMENTS, ODF_MULTI_VALUED_LOOKUPS, CMN_LOOKUPS_V tables


SELECT distinct
INV.NAME "Nombre de Proyecto" ,
clv.NAME "Gerencia Solicitante",
clv2.NAME "Project Stakeholder"
FROM
niku.INV_INVESTMENTS INV
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID
join niku.CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'MACGERENCIASOLICITANTE'
join niku.ODF_MULTI_VALUED_LOOKUPS MUL2 on INV.ID=MUL2.PK_ID
join niku.CMN_LOOKUPS_V clv2 on MUL2.VALUE=clv2.LOOKUP_CODE and clv2.LOOKUP_TYPE = 'OBJ_PROJECT_STAKEHOLDER'

NJ

Hi NJ, thanks for your assitance. I tried the query you suggested and this is what I'm getting:

If I try:

SELECT distinct
port.name "Cartera",
INV.NAME "Nombre de Proyecto" ,
clv.NAME "Gerencia Solicitante",
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) "Gestor de Proyecto",
clv2.NAME "Project Stakeholder"
FROM
niku.INV_INVESTMENTS INV
join niku.PMA_PORTFOLIO_CONTENTS cont on inv.ID=cont.INVEST_ID
join niku.PMA_PORTFOLIOS port on port.ID=cont.PORTFOLIO_ID
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID
join niku.CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'MACGERENCIASOLICITANTE'
join niku.ODF_MULTI_VALUED_LOOKUPS MUL2 on INV.ID=MUL2.PK_ID
join niku.CMN_LOOKUPS_V clv2 on MUL2.VALUE=clv2.LOOKUP_CODE and clv2.LOOKUP_TYPE = 'OBJ_PROJECT_STAKEHOLDER'

I get no results.

If I try onlu this:

SELECT distinct
port.name "Cartera",
INV.NAME "Nombre de Proyecto" ,
clv.NAME "Gerencia Solicitante",
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) "Gestor de Proyecto"
FROM
niku.INV_INVESTMENTS INV
join niku.PMA_PORTFOLIO_CONTENTS cont on inv.ID=cont.INVEST_ID
join niku.PMA_PORTFOLIOS port on port.ID=cont.PORTFOLIO_ID
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID
join niku.CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'MACGERENCIASOLICITANTE'

I get results.

It seems that it is not taking the last second join to ODF_MULTI_VALUED_LOOKUPS correctly. I'm Including a screenshot.

Do you have any idea why?

Thanks again.

Juan

juan.segovia wrote:

SELECT distinct
port.name "Cartera",
INV.NAME "Nombre de Proyecto" ,
clv.NAME "Gerencia Solicitante",
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) "Gestor de Proyecto",
clv2.NAME "Project Stakeholder"
FROM
niku.INV_INVESTMENTS INV
join niku.PMA_PORTFOLIO_CONTENTS cont on inv.ID=cont.INVEST_ID
join niku.PMA_PORTFOLIOS port on port.ID=cont.PORTFOLIO_ID
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID
join niku.CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'MACGERENCIASOLICITANTE'
join niku.ODF_MULTI_VALUED_LOOKUPS MUL2 on INV.ID=MUL2.PK_ID
join niku.CMN_LOOKUPS_V clv2 on MUL2.VALUE=clv2.LOOKUP_CODE and clv2.LOOKUP_TYPE = 'OBJ_PROJECT_STAKEHOLDER'

The table ODF_MULTI_VALUED_LOOKUPS also contains the column name of the column which is a multi-valued type.
The reason you are not getting any results for the query is you have not mentioned that column name as you are referencing the table twice.

It is not able to pick up a unique reference.
You can try the below changes to your query.

juan.segovia wrote:

join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID

Instead, write
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID AND ATTRIBUTE=<column_name>

Similarly,

juan.segovia wrote:

join niku.ODF_MULTI_VALUED_LOOKUPS MUL2 on INV.ID=MUL2.PK_ID

Instead, write
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID AND ATTRIBUTE=<column_name2>

The <column_name> will be you attribute code which is a Multi-Valued Lookup based field.

Hope this helps

I tried your suggestion and now I have this query:

SELECT distinct
port.name "Cartera",
INV.NAME "Nombre de Proyecto" ,
clv.NAME "Gerencia Solicitante",
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) "Gestor de Proyecto",
clv2.NAME "Project Stakeholder"
FROM
niku.INV_INVESTMENTS INV
join niku.PMA_PORTFOLIO_CONTENTS cont on inv.ID=cont.INVEST_ID
join niku.PMA_PORTFOLIOS port on port.ID=cont.PORTFOLIO_ID
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID AND ATTRIBUTE='mac_prj_gerenciasol'
join niku.CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'MACGERENCIASOLICITANTE'
join niku.ODF_MULTI_VALUED_LOOKUPS MUL2 on INV.ID=MUL2.PK_ID AND MUL2.ATTRIBUTE='mac_prj_coordinador'
join niku.CMN_LOOKUPS_V clv2 on MUL2.VALUE=clv2.LOOKUP_CODE and clv2.LOOKUP_TYPE = 'OBJ_PROJECT_STAKEHOLDER'

But I'm still not getting results. I only got results with this part:

SELECT distinct
port.name "Cartera",
INV.NAME "Nombre de Proyecto" ,
clv.NAME "Gerencia Solicitante",
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) "Gestor de Proyecto"
FROM
niku.INV_INVESTMENTS INV
join niku.PMA_PORTFOLIO_CONTENTS cont on inv.ID=cont.INVEST_ID
join niku.PMA_PORTFOLIOS port on port.ID=cont.PORTFOLIO_ID
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID AND ATTRIBUTE='mac_prj_gerenciasol'
join niku.CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'MACGERENCIASOLICITANTE'

Please advice.

Thanks,

Juan

Can you try this ?

SELECT distinct
port.name "Cartera",
INV.NAME "Nombre de Proyecto" ,
clv.NAME "Gerencia Solicitante",
(select FULL_NAME from niku.SRM_RESOURCES as res where inv.MANAGER_ID=res.USER_ID) "Gestor de Proyecto",
clv2.NAME "Project Stakeholder"
FROM
niku.INV_INVESTMENTS INV
join niku.PMA_PORTFOLIO_CONTENTS cont on inv.ID=cont.INVEST_ID
join niku.PMA_PORTFOLIOS port on port.ID=cont.PORTFOLIO_ID
join niku.ODF_MULTI_VALUED_LOOKUPS MUL on INV.ID=MUL.PK_ID AND [color=#3200ff]MUL.[color]ATTRIBUTE='mac_prj_gerenciasol'
join niku.CMN_LOOKUPS_V clv on MUL.VALUE=clv.LOOKUP_CODE and clv.LOOKUP_TYPE = 'MACGERENCIASOLICITANTE'
join niku.ODF_MULTI_VALUED_LOOKUPS MUL2 on INV.ID=MUL2.PK_ID AND MUL2.ATTRIBUTE='mac_prj_coordinador'
join niku.CMN_LOOKUPS_V clv2 on MUL2.VALUE=clv2.LOOKUP_CODE and clv2.LOOKUP_TYPE = 'OBJ_PROJECT_STAKEHOLDER'


NJ

NJ

I'm still not getting any results with the modification you suggested.

I found the problem here but I do have a new question now:

The problem is that the OBJ_PROJECT_STAKEHOLDER attribute I'm trying to bring here is a Clarity ootb Dynamic Query Attribute.

It uses this quey:

SELECT @SELECT:RESOURCES.ID:ID@,
@SELECT:RESOURCES.LAST_NAME:LAST_NAME@,
@SELECT:RESOURCES.FIRST_NAME:FIRST_NAME@,
@SELECT:RESOURCES.FULL_NAME:FULL_NAME@,
@SELECT:RESOURCES.UNIQUE_NAME:UNIQUE_NAME@,
@SELECT:RESOURCES.UNIQUE_NAME:UNIQUE_CODE@
FROM SRM_RESOURCES RESOURCES
WHERE 1=1
AND @WHERE:SECURITY:RESOURCE:RESOURCES.ID@
AND @FILTER@
AND RESOURCES.PERSON_TYPE != 0
@BROWSE-ONLY:
AND RESOURCES.IS_ACTIVE = 1
:BROWSE-ONLY@

Do you have any ideas on how an I modify the query to bring that values to my portlet?

Thanks,

Juan