Clarity

  • 1.  How to list all Portlets used by an NSQL query ?

    Posted Jan 11, 2010 02:28 PM
    EIther within Clarity Studio, or at Oracle TABLE level, how can I get a list of all Portlets that utilise an NSQL query?We have to structurally modify an NSQL query, and I need to be able to see all Portlets affected by it.Any advice appreciated.ThanksMarlon.


  • 2.  Re: How to list all Portlets used by an NSQL query ?
    Best Answer

    Posted Jan 11, 2010 06:50 PM
    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]  


  • 3.  Re: How to list all Portlets used by an NSQL query ?

    Posted Jan 17, 2010 08:21 PM
    Thanks, Paul...works a treat!Cheers,Marlon.