ALERT: Some images may not load properly within the Knowledge Base Article. If you see a broken image, please right-click and select 'Open image in a new tab'. We apologize for this inconvenience.

Clarity: Creating a power filter for an NSQL portlet generates the generic Error 500.

book

Article ID: 51541

calendar_today

Updated On:

Products

CA Identity Manager CA Identity Governance CA Identity Portal Clarity PPM SaaS Clarity PPM On Premise

Issue/Introduction

Description:

The NSQL construction SUM(DECODE(R.ID,4,0,NVL(R.ID,0)/ .. causes Error 500 on a portlet when used as a power filter.

Steps to Reproduce:

  1. Create a Clarity Query
    SELECT @SELECT:DIM:USER_DEF:IMPLIED:RESOURCE:R.FULL_NAME:[email protected], 
         @SELECT:DIM_PROP:USER_DEF:IMPLIED:RESOURCE:MR.FULL_NAME:[email protected], 
@SELECT:METRIC:USER_DEF:IMPLIED:SUM(DECODE(R.ID,4,0,NVL(R.ID,0)/(CASE WHEN 
R.ID <> 0 THEN 1 ELSE 3600 END))):[email protected] 
FROM INV_INVESTMENTS I, 
         SRM_RESOURCES R, 
         SRM_RESOURCES MR, 
         CMN_SEC_USERS U 
WHERE I.CREATED_BY = U.ID 
AND U.ID = R.USER_ID 
AND R.MANAGER_ID = MR.USER_ID 
AND @[email protected] 
GROUP BY R.FULL_NAME, 
         MR.FULL_NAME 
HAVING @[email protected]
  2. Create a Clarity portlet where the AGG attribute is in the filter section

  3. Use the portlet

Expected: The user should be able to filter according to AGG attribute.
Actual: Error 500 - Internal Server Error.

Error from app-niku.log file:

Caused by: java.sql.SQLException: [CA Clarity][Oracle JDBC Driver][Oracle]ORA-00934: group function is not allowed here

Solution:

This issue has been documented as CLRT-43004 and is assigned to development for review.

Keywords: CLARITYKB, error, power, filter, NSQL, clarity8open, clarity12open.

Environment

Release: NCSVCS05900-8.1-Clarity-Service Connect-for MS-SQL
Component:
Powered by Wolken Software