Clarity

 View Only
Expand all | Collapse all

Attribute with dynamic lookup query - include in data warehouse

  • 1.  Attribute with dynamic lookup query - include in data warehouse

    Posted Jun 01, 2017 07:10 AM

    Hello,

     

    We need to add one custom attribute based on lookup with dynamic query to data warehouse, but I still got following message:

    The below attributes could not be included in the Data Warehouse. If the attribute is lookup, query is not DWH compatible.

    I found here this thread CA PPM Tech Tip: Including Dynamic Query Lookup Attributes in the Data Warehouse 

    So I added column LAST_UPDATED_DATE, but still the same problem.

     

    Here is my query:

    select
    @SELECT:l.lookup_code:lookup_code@,
    @SELECT:nls.name:lookup_value@,
    @SELECT:l.LOOKUP_ENUM:LOOKUP_ENUM@,
    @SELECT:SYSDATE:LAST_UPDATED_DATE@
    from cmn_lookups l
    left join cmn_captions_nls nls on l.id=nls.pk_id
    left join odf_ca_c_proces_map map on map.c_proces_map_pr=l.lookup_code
    where l.lookup_type='C_L_PROCES' and nls.language_code='en'
    and nls.table_name='CMN_LOOKUPS'
    and  (
    case when @WHERE:PARAM:USER_DEF:STRING:inv_type@ is null then '***' else upper(@WHERE:PARAM:USER_DEF:STRING:inv_type@) end = nvl(c_pp_proc_inv_typ,'***')
    and
    case when @WHERE:PARAM:USER_DEF:STRING:projekt_type@ is null then 'aaaaa' else @WHERE:PARAM:USER_DEF:STRING:projekt_type@ end = nvl(c_proces_map_pt,'aaaaa')
    )
    and map.c_pp_proc=1
    and @FILTER@
    order by l.LOOKUP_ENUM

    Any ideas how to solve this?

     

    Thank you all in advance,

     

    Martin



  • 2.  Re: Attribute with dynamic lookup query - include in data warehouse
    Best Answer

    Posted Jun 01, 2017 08:53 AM

    Hi msnizek , 

     

    You need to add language code and language id as well. After adding these two in you select cloumn it should work fine.

     

    @SELECT:c.LAST_UPDATED_DATE:LAST_UPDATED_DATE@,

    @SELECT:LANG.LANGUAGE_CODE:LANGUAGE_CODE@,

    @SELECT:LANG.ID:LANGUAGE_ID@

     

    Regards,

    Prashank Singh



  • 3.  Re: Attribute with dynamic lookup query - include in data warehouse

    Posted Jun 01, 2017 11:48 AM

    Hello Prashank,

     

    thank you, it works now.

     

    Regards,

    Martin