Clarity

  • 1.  Pulling Static Dependent Lookup Values with Query?

    Posted Aug 04, 2010 02:29 PM
    Hi...
    Using v.12 and looking for the path of least resistance for pulling the entire static dependent lookup value into a query.

    We have 3 levels and I don't want to just see the bottom. Some of them are the same across different levels (for example - IT/CRM/Billing and IT/RMO/Billing)

    The ODF_MULTI_VALUED_LOOKUP table just shows the lowest level. Where can I find the path?

    Thanks...
    Andrea


  • 2.  RE: Pulling Static Dependent Lookup Values with Query?

    Posted Aug 05, 2010 03:26 AM
    The below query was devised by us to get a path of a Static Dependent Lookup with 2 levels:

    SELECT
    c.id id,
    c.lookup_code lookup_code,
    (

    (

    SELECT



    Name

    FROM



    cmn_captions_nls

    WHERE



    table_name = 'CMN_LOOKUPS'

    AND
    language_code = 'en'

    AND
    pk_id =





    (





    SELECT id





    FROM cmn_lookups





    WHERE lookup_code = c.parent_lookup_code





    AND lookup_type = <Parent_Level_Lookup_Code>





    )

    )

    + '/' +

    (

    SELECT Name

    FROM cmn_captions_nls

    WHERE table_name = 'CMN_LOOKUPS'

    AND language_code = 'en'

    AND pk_id = c.id

    )
    ) Multi_Value_Path
    FROM cmn_lookups c
    WHERE lookup_type = <Lowest_Level_Lookup_Code>

    This query would give you a path such as IT/CRM, Business/Services etc.

    You can add another section in the above query to get the third level.

    Hope this helps.

    Regards
    ~Dev


  • 3.  RE: Pulling Static Dependent Lookup Values with Query?

     
    Posted Aug 05, 2010 11:18 AM
    Thanks Dev!

    Chris


  • 4.  RE: Pulling Static Dependent Lookup Values with Query?

    Posted Apr 03, 2014 10:09 AM

    What if there are multiple levels ?

    How can we make the query dynamic in that case ?

    NJ



  • 5.  RE: Pulling Static Dependent Lookup Values with Query?
    Best Answer

    Posted Apr 04, 2014 02:46 AM

    Would something like this work?
    (Obviously you can remove the row number part if your not putting it in a nsql query, thus not needing a unique value for id)
    The replace part is because my system has / in one of the names of the lookup values, so i couldn't use it as a path marker...

    SELECT
    ROW_NUMBER() OVER (ORDER BY CMN.ID) RNUM
    , CMN.ID Internal_ID
    , CMN.LOOKUP_CODE Lookup_Code
    , CAP.NAME Lookup_Name
    , LTRIM(SYS_CONNECT_BY_PATH(REPLACE(CAP.NAME, '/', '\'), '/'),'/') Path
    FROM CMN_LOOKUPS CMN
    JOIN CMN_CAPTIONS_NLS CAP ON CMN.ID = CAP.PK_ID AND CAP.TABLE_NAME = 'CMN_LOOKUPS' AND CAP.LANGUAGE_CODE = 'en'
    WHERE LEVEL > 1
    CONNECT BY PRIOR CMN.LOOKUP_CODE = CMN.PARENT_LOOKUP_CODE

    Hope it helps!

    Chris