How do I get the company name on this query

[Juan A. Segovia]

Hi, I'm working with the license portlet trying to add the COMPANY NAME there.

 

I'm able to get the COMPANY ID, but I cannot seem to get the NAME related to that ID:

 

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCES:cast(r.id as VARCHAR):dummy_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.last_name:Last_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.user_name:User_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:r.unique_name:id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.first_name:First_Name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.company_id:Company@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:l.name:Name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.last_logged_in_date:Last_Logged@
       
FROM   (SELECT  max(licence_priority) max_priority,
                    user_id
        FROM     cmn_lic_users_v
        GROUP BY user_id) countTab,
       cmn_lookups_v l,
       cmn_sec_users u, SRM_RESOURCES r
WHERE  l.sort_order = countTab.max_priority
AND    l.lookup_type = 'CMN_LIC_RIGHT_TYPE'
AND    u.id = countTab.user_id
AND    u.user_status_id = 200
AND    u.id = r.user_id
AND    l.language_code = @WHERE:PARAM:LANGUAGE@
AND    @FILTER@

 

What do I need in there to get that?

 

Regards,

 

Juan.

[Deactivated User]

You need to get it from the cmn_captions_nls table

See

Lookup name in DB 

for an example

[Juan A. Segovia]

Hi Urnas, I was finally able to get this this way:

 

SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCES:cast(r.id as VARCHAR):dummy_id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.last_name:Last_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.user_name:User_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:r.unique_name:id@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.first_name:First_Name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.company_id:Company@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:c.company_name:Company_name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:l.name:Name@,
       @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCES:u.last_logged_in_date:Last_Logged@
       
FROM   (SELECT  max(licence_priority) max_priority,
                    user_id
        FROM     cmn_lic_users_v
        GROUP BY user_id) countTab,
       cmn_lookups_v l, srm_companies c,
       cmn_sec_users u, SRM_RESOURCES r
WHERE  l.sort_order = countTab.max_priority
AND    l.lookup_type = 'CMN_LIC_RIGHT_TYPE'
AND    u.id = countTab.user_id
AND    u.company_id = c.id
AND    u.user_status_id = 200
AND    u.id = r.user_id
AND    l.language_code = @WHERE:PARAM:LANGUAGE@
AND    @FILTER@