Marlon, try this it should help I think (I had to remove a reference to a non-standard view and as a consequence might return some unwanted rows - but overall it should be sufficient for your need). select
*
from (
select distinct
dp.portlet_id
, nls.name portlet_name
, dp.data_provider_code
, dp.dal_type
, cp.portlet_code
, cp.source
, cp.category_id
, cat.name as category_name
, lpt.name as portlet_type_name
, cp.is_active
, cp.is_available
, cp.portlet_type_code
, cp.instance_type
, case when dp.cnq_id is null then dp.cnq_id else obj_id end as provider_id
, case when dp.cnq_id is null and obj_code is null then 0 else 1 end as is_provider_link
, nls.description
from (
select
cg.id
, cg.portlet_id
, case when cg.dal_type = 'nsql' then cgnq.query_code else dal_code end as data_provider_code
, cg.dal_type
, cg.dal_id
, cg.dal_code
, cgnq.id cnq_id -- NB! fiddle
, cgnq.query_code
, o.id as obj_id
, o.code as obj_code
from cmn_graphs cg
left join cmn_nsql_queries cnq on dal_id = cnq.id
left join cmn_gg_nsql_queries cgnq on cnq.id = cgnq.cmn_nsql_queries_id
left join odf_objects o on cg.dal_code = o.code
/*
( @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NOT NULL AND cnq.nsql_text LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:q_text@ || '%'
OR @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NULL )
*/
union all
select
cg.id
, cg.portlet_id
, case when cg.dal_type = 'nsql' then cgnq.query_code else dal_code end as data_provider_code
, cg.dal_type
, cg.dal_id
, cg.dal_code
, cgnq.id cnq_id -- NB! fiddle
, cgnq.query_code
, o.id as obj_id
, o.code as obj_code
from cmn_grids cg
left join cmn_nsql_queries cnq on dal_id = cnq.id
left join cmn_gg_nsql_queries cgnq on cnq.id = cgnq.cmn_nsql_queries_id
left join odf_objects o on cg.dal_code = o.code
/*
( @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NOT NULL AND cnq.nsql_text LIKE '%' || @WHERE:PARAM:USER_DEF:STRING:q_text@ || '%'
OR @WHERE:PARAM:USER_DEF:STRING:q_text@ IS NULL )
*/
) dp
join cmn_portlets cp on dp.portlet_id = cp.id
join cmn_captions_nls nls on cp.id = nls.pk_id and nls.table_name = 'CMN_PORTLETS' and language_code = 'en'
join (select * from cmn_lookups_v where lookup_type = 'PORTLET_TYPE' and language_code = 'en' ) lpt on lpt.lookup_code = cp.portlet_type_code
join (select * from cmn_lookups_v where lookup_type = 'CMN_CATEGORIES' and language_code = 'en' and lookup_code != 'framework') cat on cat.id = cp.category_id
) portlets
where 1=1 This query should identify the data provider for any grid or graphic portlet. [Oracle syntax]