Clarity PPM1

Expand all | Collapse all

How do I get the company name on this query

Jump to Best Answer
  • 1.  How do I get the company name on this query

    Posted 04-15-2019 02:23 PM

    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.



  • 2.  Re: How do I get the company name on this query

    Posted 04-15-2019 02:57 PM

    You need to get it from the cmn_captions_nls table

    See

    Lookup name in DB 

    for an example



  • 3.  Re: How do I get the company name on this query
    Best Answer

    Posted 04-15-2019 03:59 PM

    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@