AnsweredAssumed Answered

Attribute with dynamic lookup query - include in data warehouse

Question asked by msnizek on Jun 1, 2017
Latest reply on Jun 1, 2017 by msnizek

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

Outcomes