Cannot filter Knowledge Documents by category using data partitions

Products

• CA Service Management - Service Desk Manager
• CA Service Desk Manager

Issue/Introduction

Requirement to use a mix of permissisions by role and also by specific groups on a Knowledge Category

Requirements:
1. Users with the new role do not belong to any group. They are affected users that can only create tickets - they do not manage or solve them.
2. These users must only see the new knowledge category and its documents. They must NOT see the other Knowledge categories or documents.
3. The Knowledge category must also be seen by users that belong to some other specific groups. These users have other roles and will be in charge of adding and mantaining the documents in the Knowledge category.

When attempting to use data partition constraints to achieve the above requirements, the following is observed

1.  Create a new Knowledge category.  Record its ID and Knowledge Category name

2.  Edit an existing Data Partition and change the 'View' restriction for the 'O_INDEXES' table. 

VIEW  O_INDEXES CAPTION = 'KB Category Caption'
 
For example, O_INDEXES CAPTION = 'TEMP'
 
3.  Assign this Data Partition to the user(s).  This works fine - the user(s) will see only the Knowledge category with that caption.

4.  Edit the same Data Partition and change the 'View' restriction for the 'SKELETONS' table

VIEW SKELETONS DOC_LINKS.INDEX_ID = DOC_ID

For example, VIEW SKELETONS DOC_LINKS.INDEX_ID = 400002
 
5.  When the user(s) attempt to access the Knowledge tab, no knowledge documents appear within the Knowledge Category, there is no error within the UI and the following errors are seen in the STDLOG

When using an ORACLE MDB

sqlagt:select21         8768 ERROR        orclclass.c           1313 SQL Statement Failed. ORA: 932  Message: ORA-00932: inconsistent datatypes: expected - got NCLOB 

sqlagt:select21         8768 ERROR        orclclass.c           1314 SELECT DISTINCT SKELETONS.ID, SKELETONS.TITLE, SKELETONS.RESOLUTION_SHORT, SKELETONS.KS_TYPE, SKELETONS.STATUS_ID, SKELETONS.DOC_TYPE_ID, SKELETONS.MODIFY_DATE, SKELETONS.REWORK_VER, CI_STATUSES.STATUS, CI_DOC_TEMPLATES.HIDE_S, SKELETONS.SUMMARY, CI_DOC_TEMPLATES.HIDE_P, SKELETONS.PROBLEM, SKELETONS.TITLE, SKELETONS.RESOLUTION_SHORT, SKELETONS.KS_TYPE, SKELETONS.STATUS_ID, SKELETONS.DOC_TYPE_ID, SKELETONS.MODIFY_DATE, SKELETONS.REWORK_VER, CI_STATUSES.STATUS, CI_DOC_TEMPLATES.HIDE_S, SKELETONS.SUMMARY, CI_DOC_TEMPLATES.HIDE_P, SKELETONS.PROBLEM FROM (SKELETONS LEFT JOIN CI_STATUSES ON SKELETONS.STATUS_ID = CI_STATUSES.ID), CI_DOC_TEMPLATES, INDEX_DOC_LINKS WHERE ( ( ( SKELETONS.ID IN (400002,400001) ) ) AND ( INDEX_DOC_LINKS.DOC_ID = SKELETONS.ID AND INDEX_DOC_LINKS.INDEX_ID = 400002 ) ) AND SKELETONS.DOC_TEMPLATE_ID = CI_DOC_TEMPLATES.ID

sqlagt:select21         8768 ERROR        orcl.c                1762  Oracle error #: 0

sqlagt:select21         8768 ERROR        orcl.c                1763  Oracle msg #: 0

bpvirtdb_srvr           4084 ERROR        vdbmisc.c              688 Miscellaneous Database error occured :ORA-00932: inconsistent datatypes: expected - got NCLOB 

domsrvr                 9000 ERROR        domset.c              8966 Error in display_by_index method select_response:  AHD04199:An unexpected Database error occurred. Contact your administrator.

web:local               7692 ERROR        parse.c               8513 display_by_index failed for PDM_LIST at lsEnd.mac[28]:  AHD04199:An unexpected Database error occurred. Contact your administrator.

When using a SQL MDB

sqlagt:select22         7988 ERROR           sqlclass.c            1016 SQL Execute  failed: [Microsoft SQL Server Native Client 11.0] [ SQL Code=421 SQL State=42000] The ntext data type cannot be selected as DISTINCT because it is not comparable.; [Microsoft SQL Server Native Client 11.0] [ SQL Code=421 SQL State=42000] The ntext data type cannot be selected as DISTINCT because it is not comparable.

sqlagt:select22         7988 ERROR        sqlclass.c            1017 Clause (SELECT DISTINCT SKELETONS.ID, SKELETONS.TITLE, SKELETONS.RESOLUTION_SHORT, SKELETONS.KS_TYPE, SKELETONS.STATUS_ID, SKELETONS.DOC_TYPE_ID, SKELETONS.MODIFY_DATE, SKELETONS.REWORK_VER, CI_STATUSES.STATUS, CI_DOC_TEMPLATES.HIDE_S, SKELETONS.SUMMARY, CI_DOC_TEMPLATES.HIDE_P, SKELETONS.PROBLEM, SKELETONS.TITLE, SKELETONS.RESOLUTION_SHORT, SKELETONS.KS_TYPE, SKELETONS.STATUS_ID, SKELETONS.DOC_TYPE_ID, SKELETONS.MODIFY_DATE, SKELETONS.REWORK_VER, CI_STATUSES.STATUS, CI_DOC_TEMPLATES.HIDE_S, SKELETONS.SUMMARY, CI_DOC_TEMPLATES.HIDE_P, SKELETONS.PROBLEM FROM (SKELETONS LEFT JOIN CI_STATUSES ON SKELETONS.STATUS_ID = CI_STATUSES.ID), CI_DOC_TEMPLATES, INDEX_DOC_LINKS WHERE ( ( ( SKELETONS.ID IN (400001,402952,402951) ) ) AND ( INDEX_DOC_LINKS.DOC_ID = SKELETONS.ID AND INDEX_DOC_LINKS.INDEX_ID = 400001 ) ) AND SKELETONS.DOC_TEMPLATE_ID = CI_DOC_TEMPLATES.ID) Input (<None>)

sqlagt:select22         7988 MILESTONE    sqlclass.c            1063 The following statement took 15 milliseconds: Clause (SELECT DISTINCT SKELETONS.ID, SKELETONS.TITLE, SKELETONS.RESOLUTION_SHORT, SKELETONS.KS_TYPE, SKELETONS.STATUS_ID, SKELETONS.DOC_TYPE_ID, SKELETONS.MODIFY_DATE, SKELETONS.REWORK_VER, CI_STATUSES.STATUS, CI_DOC_TEMPLATES.HIDE_S, SKELETONS.SUMMARY, CI_DOC_TEMPLATES.HIDE_P, SKELETONS.PROBLEM, SKELETONS.TITLE, SKELETONS.RESOLUTION_SHORT, SKELETONS.KS_TYPE, SKELETONS.STATUS_ID, SKELETONS.DOC_TYPE_ID, SKELETONS.MODIFY_DATE, SKELETONS.REWORK_VER, CI_STATUSES.STATUS, CI_DOC_TEMPLATES.HIDE_S, SKELETONS.SUMMARY, CI_DOC_TEMPLATES.HIDE_P, SKELETONS.PROBLEM FROM (SKELETONS LEFT JOIN CI_STATUSES ON SKELETONS.STATUS_ID = CI_STATUSES.ID), CI_DOC_TEMPLATES, INDEX_DOC_LINKS WHERE ( ( ( SKELETONS.ID IN (400001,402952,402951) ) ) AND ( INDEX_DOC_LINKS.DOC_ID = SKELETONS.ID AND INDEX_DOC_LINKS.INDEX_ID = 400001 ) ) AND SKELETONS.DOC_TEMPLATE_ID = CI_DOC_TEMPLATES.ID) Input (<None>)
bpvirtdb_srvr           7328 ERROR        vdbmisc.c              688 Miscellaneous Database error occured :[Microsoft SQL Server Native Client 11.0] [ SQL Code=421 SQL State=42000] The ntext data type cannot be selected as DISTINCT because it is not comparable.; [Microsoft SQL Server Native Client 11.0] [ SQL Code=421 SQL State=42000] The ntext data type cannot be selected as DISTINCT because it is not comparable.

domsrvr                 8088 ERROR        domset.c              8966 Error in display_by_index method select_response:  AHD04199:An unexpected Database error occurred. Contact your administrator.

web:local               6160 ERROR        parse.c               8513 display_by_index failed for PDM_LIST at lsEnd.mac[28]:  AHD04199:An unexpected Database error occurred. Contact your administrator.

Environment

CA Service Desk Manager 17.3 and 17.4

All Supported Operating Systems and Oracle and SQL DBMS

Cause

The root cause of the issue is due to the SQL query having DISTINCT clause on the NTEXT type attribute 'PROBLEM'.

Resolution

Perform the following workaround:

1.  Using Web Screen Painter (WSP), make a custom version of the file 

NX_ROOT\bopcfg\www\htmpl\web\analyst\list_KD.htmpl

2.  In the custom version of the file, locate and comment out the following two (2) lines

<PDM_MACRO name=lsWrite text="pdm_elif '@{list.DOC_TEMPLATE_ID.HIDE_P:}' != '1'">

<PDM_MACRO name=lsCol label="Problem" attr="PROBLEM" colspan=4 disp_entities=no export=no fmtfunc=highlightSearchTerms sort=no startrow=yes width=255>

3.  Save and publish custom form

4.  Clear web browser cache