How to Query Static List Lookup Values

Document ID : KB000028436
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

What table are the display names in for each value in the Lookup?  How can we query the Lookups to show a list of values for use in reporting or portlets?

Solution:

Here is a simple query that will provide you with the English translation of the Lookup Value Name for the stock static lookup 'Investment Goal Lookup'.

     SELECT L.LOOKUP_TYPE, L.LOOKUP_CODE, N.NAME, N.DESCRIPTION, L.IS_ACTIVE, L.PARTITION_CODE, L.PARTITION_MODE_CODE
FROM CMN_LOOKUPS L, CMN_CAPTIONS_NLS N
WHERE L.ID = N.PK_ID
AND N.LANGUAGE_CODE = 'en'
AND N.TABLE_NAME = 'CMN_LOOKUPS'
AND L.LOOKUP_TYPE = 'INVESTMENT_GOAL_TYPE'

Note: Change L.LOOKUP_TYPE with the correct ID of your lookup or any other static list lookup.  Also, you can modify the query for use in a portlet to retrieve the current user's language setting.  Look at some example stock portlet queries to see how this can be accomplished.

If you need more assistance in developing custom queries or portlets, please contact your CA Services Representative.