MaxH Honored Contributor.
Honored Contributor.

Portlet Filter

Looking for assistance with a portlet query that is breaking.  The query is:


select * from ( SELECT request_id REQUEST_ID, last_update_date LAST_UPDATE, description DESCRIPTION, request_type_name REQUEST_TYPE, status_name STATUS, workflow_name WORKFLOW
, row_number() over ( ORDER BY request_id ASC ) as record_index FROM KCRT_PORTLET_REQUEST_LIST_V
WHERE request_id in (select request_id from kcrt_requests kr
where (status_code NOT LIKE 'CLOSED%' AND status_code NOT LIKE 'CANCEL%')
AND ((assigned_to_user_id = ? AND exists (select 'Is eligible' from knta_eligibility_check_v where request_id = parent_id and user_id = ? and instance_source_type_code = 'IR'))
OR created_by = ?))
and request_type_id in '[REQ.TYPE]'
) where record_index between ? and ?


The query is supposed to take list of request types (taken from the CRT - Request Types - Enabled validation) and allow the user to be able to filter on them so he/she can choose one or multiples of the request types for thier own dashboard.  My filter is calling this from the query:


and request_type_id in '[REQ.TYPE]'


Any assistance is appreciated.





3 Replies
Absent Member.. alex-h Absent Member..
Absent Member..

Re: Portlet Filter

Hello, it's normal after all.


Binding didn't support multi-valuated values.


Replace :

and request_type_id in '[REQ.TYPE]'


By :

AND request_type_id IN (SELECT    regexp_substr ( [P.TYPE] , CHR(91) ||'^,' || CHR(93) || '+' , 1 , LEVEL )
        FROM    dual
        CONNECT BY    regexp_substr ( [P.TYPE] , CHR(91) ||'^,' || CHR(93) || '+', 1 , LEVEL ) IS NOT NULL


This sub-select clause will explode P.TYPE variable into multiple rows.

Absent Member.. AlfredoMonasi Absent Member..
Absent Member..

Re: Portlet Filter

You could also do a INSTR('[P.TOKEN]',ID_FIELD)>1 in your where clause.

Absent Member.. AlexSavencu Absent Member..
Absent Member..

Re: Portlet Filter



these are too complicated.


The following works:


and request_type_id in ([P.REQ_TYPE_ID]) - of course, as long as REQ_TYPE_ID  is number.




--remember to kudos people who helped solve your problem
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.