Clarity

Hi,

Any idea which table the relationship between portlets and its dataproviders are stored(Query ID)?

Could find the Query details in the below tables :
CMN_NSQL_QUERY_FILTERS
CMN_GG_NSQL_QUERIES
NBI_CFG_STOPLIGHT_QUERIES
CMN_NSQL_QUERIES

Portlet Details in the below tables:
CMN_PAGE_PORTLETS
CMN_PORTLETS

Looking for the relationship between Queries and Portlets

- Vamsi

This will hopefully get you started:

select p.id, p.portlet_code, p.source,

g.portlet_instance_id, g.principal_type, g.principal_id,
substr(q.nsql_text, 1, 300) || '...' as nsql_preview
from cmn_portlets p
join cmn_grids g on p.id = g.portlet_id
join cmn_nsql_queries q on g.dal_id = q.id and g.dal_type = 'nsql'


If you can perform a Clarity SQL Trace of the General properties page of a portlet where its Data Provider is showing, the trace file generated would yield the queries that Clarity itself uses to derive that information from the database. Neither that or this query provided is intended to cover all bases of the datamodel involved though.

Any idea on what "dal" stands for in table cmn_grids? like dal_id, dal_type, dal_partition_code..

Officially, I don't exactly know what 'dal' stands for, but given what is it used for, I believe it means Data Abstraction Layer (or else something similar and with the same meaning).

Nick,

The above query helps. Thanks. Also, could you plz let us know the tables for portlets and queries from where we can find their respective names..i.e. Portlet Name and Query Name

Uruj,


You can get the details from CMN_CAPTIONS_NLS table. Hope the below query might help you.

SELECT NL.NAME, CMN.PORTLET_CODE,NL.DESCRIPTION FROM CMN_CAPTIONS_NLS NL,
CMN_PORTLETS CMN
WHERE NL.LANGUAGE_CODE = 'en'
AND CMN.ID = NL.PK_ID
AND NL.TABLE_NAME = 'CMN_PORTLETS'
AND CMN.ID IN
(SELECT DISTINCT(PORTLET_ID) FROM CMN_GRIDS WHERE UPPER(PORTLET_ID) LIKE '%XXXXX%')
Order by cmn.id