Aurora_Gaimon

CA PPM: Good Practices when coding Lookups in SQL

Blog Post created by Aurora_Gaimon Employee on Mar 30, 2017

Sometimes I see portlets or queries which are getting IDs instead of VALUE names. The issues appear when value names are renamed to something else. Then portlet, reports or queries will not reflect automatically the changes.

 

Let's see an example how to code value names instead of value IDs.

 

 

Lookup:

Create a look with several values.

 

 

 

Now, if for any reason a value name is renamed, for example from Apples to Green Apples, we want to ensure the change is automatically captured in portlets, reports, etc..

 

 

 

So, when adding a lookup in your SQL Code,  ALWAYS relay on captions/names:

 

Single Value Lookup:

Create a single lookup:

 

 

 

Multi Value Lookup (MVL):

Create a MVL lookup:

 

 

 

 

We set some values in a project instance:

 

 

 

 

And when writing the SQL query, we need to ensure we always use CAPTIONS and their NAMES:

 

SELECT 
(SELECT CAP.NAME
  FROM CMN_LOOKUPS L JOIN CMN_CAPTIONS_NLS CAP ON CAP.PK_ID = L.ID AND CAP.TABLE_NAME = 'CMN_LOOKUPS' AND CAP.LANGUAGE_CODE = 'en'
  WHERE LOOKUP_TYPE = 'Z_DEMO_LKP'
AND LOOKUP_CODE = OCP.Z_FRUIT)  FRUIT
FROM ODF_CA_PROJECT OCP
WHERE ID=5002002 -- ID USED FOR DEMO

 

Results:

 

Now, if we want to get MVL lookup too, then:

 

SELECT (SELECT CAP.NAME 
          FROM CMN_LOOKUPS L
          JOIN CMN_CAPTIONS_NLS CAP ON CAP.PK_ID = L.ID AND CAP.TABLE_NAME = 'CMN_LOOKUPS' AND CAP.LANGUAGE_CODE = 'en'
          WHERE LOOKUP_TYPE = 'Z_DEMO_LKP' AND LOOKUP_CODE = OCP.Z_FRUIT)  FRUIT_SINGLE_VALUE
, FRUITS_MVL.NAME FRUITS_MVL
FROM INV_INVESTMENTS INV
LEFT JOIN  ODF_CA_PROJECT OCP ON INV.ID=OCP.ID
LEFT JOIN ( SELECT  A.PK_ID PK_ID, LISTAGG(B.NAME, ';') WITHIN GROUP (ORDER BY B.NAME) AS NAME
               FROM (SELECT PK_ID, VALUE FROM ODF_MULTI_VALUED_LOOKUPS WHERE ATTRIBUTE = 'z_fruits_mvl') A
               LEFT JOIN      (
                                   SELECT L.NAME, L.LOOKUP_CODE
                                   FROM (SELECT DISTINCT(VALUE)
                                         FROM ODF_MULTI_VALUED_LOOKUPS LKP
                                         WHERE LKP.OBJECT = 'project'
                                         AND LKP.ATTRIBUTE = 'z_fruits_mvl') MVL
                                   JOIN CMN_LOOKUPS_V L ON MVL.VALUE= L.LOOKUP_CODE  AND LANGUAGE_CODE = 'en') B ON A.VALUE=B.LOOKUP_CODE      
                                   GROUP BY A.PK_ID
                              ) FRUITS_MVL ON FRUITS_MVL.PK_ID=INV.ID 
WHERE INV.ID=5002002 -- ID USED FOR DEMO

 

Results:

 

 

We can see also the Multi Value Lookup can get the value names. I see that not really applied often and causing issues in portlets when values are renamed.

 

 

 

If you need help writing SQL queries, engage CA Services or ask questions in CA Communities.

 

That’s all. Thanks for reading until here. Did you like it? Please, don’t be shy and share it.

Outcomes