Project and Portfolio Management Practitioners Forum
cancel

Portlet Query Issue

SOLVED
Go to solution
Highlighted
brm2015
Super Contributor.

Portlet Query Issue

Hello All, 

Facing an issue that in dashboard portlet is not loading properly as giving error as misconfigured query but when I try to check the data source in workbench. Query shown in dashborad is different than query configured in the workbench. How to find the query which is shown in the dashboard? If I run the query which is shown in dashboard with modifications am able to run in db perfectly. But issue is that am unable to find the query in workbench. Is there any other way such kind of queries can be configured.

Analytic function in the query shown in dashboard is not visible in workbench query, where am facing issue to fix the issue. 

Query in front end is like 'select * from (subquery with analytic function) where condition' but the query in workbench is that the subquery from the above query is configured, am not able to find the select * from and where condition and analytic function row added to subquery. How to chase other lines information. Thanks for your help. 

 

Thanks,

BRM

 

4 REPLIES
Erik Cole
Acclaimed Contributor.

Re: Portlet Query Issue

Hi,

Your sql in the workbench gets transformed in the dashboard from yourquery to select * from (yourquery with analytic function) where condition. This is part of the recent changes to improve performance.

Have you identified which data source it is? Can you paste the error message?

 

brm2015
Super Contributor.

Re: Portlet Query Issue

Hello Eric,

Yes I have identified the data source and based on that only saying query configuration is different than showing in dashboard portlet error.

Below is the error in dashboard:

Cannot load this portlet's data because of a misconfigured SQL query. (KNTA-10836) Please contact your administrator with the following information:
-- The following error is thrown by the database:
ORA-30483: window functions are not allowed here

and the query in front end is

SELECT *
FROM
  (SELECT master_project_name MASTER_PROJECT,
    Child_project_name CHILD_PROJECT,
    nature_dependency NATURE_DEPENDENCY,
    status STATUS,
    priority_name PRIORITY,
    TO_CHAR(to_date(SUBSTR(due_date,1,10),'YYYY-MM-DD'),'Month DD,YYYY') DUE_DATE,
    Checkpoint_date CHECKPOINT_DATE,
    impact_outcome IMPACT_OUTCOME,
    action_plan ACTION_PLAN,
    Owner_name OWNER,
    last_update_date LAST_UPDATE_DATE,
    Comments COMMENTS,
    Creation_date CREATION_DATE,
    ROW_NUMBER() over (order by priority ASC) SRNO,
    master_project_id MASTER_HYPERLINK,
    child_project_id CHILD_HYPERLINK,
    (SELECT visible_parameter4
    FROM kcrt_req_header_details
    WHERE request_id=master_project_id
    ) MASTER_IT_TOWER,
    (SELECT visible_parameter4
    FROM kcrt_req_header_details
    WHERE request_id=child_project_id
    ) CHILD_IT_TOWER,
    detail_description DETAIL_DESRIPTION,
    created_by CREATED_BY,
    (SELECT pm_utils.get_project_manager_name_list(child_prj_project_id)
    FROM dual
    ) CHILD_PM ,
    row_number() over ( ORDER BY ROW_NUMBER() over (order by priority ASC) ASC ) AS record_index
  FROM cust_it_project_dependencies
  WHERE 1                 =1
  AND status              ='Open'
  AND (master_program_id IN (30043)
  OR child_program_id    IN (30043))
  )
WHERE record_index BETWEEN ? AND ?

but in the data source I didn't find row_number() over (order by row_number() over(order by priority ASC) ASC) as record_index and the where condition record_index between ? and ?.

If I ran the above query by removing the above mentioned condition it is executing successfully. Getting the same error in sql developer with the above condition.

 

Thanks,

BRM

alex-h
Super Contributor.
Solution

Re: Portlet Query Issue

Hi,

 

You can try to add /*NOPAGINATION*/ anywhere in data source. This hidden parameter will remove analytic function.

This is documented since PPM 9.22 release notes, probably.

 

Kind regards,

Alex-H

brm2015
Super Contributor.

Re: Portlet Query Issue

Hi Alex,

Thanks for your help. Issue resolved.

Thanks,
BRM