Project and Portfolio Management Practitioners Forum
cancel
Showing results for 
Search instead for 
Did you mean: 

Portlet Filter

Highlighted
MaxH
Member

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.

 

Thanks

 

Max

3 REPLIES
alex-h
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.

Re: Portlet Filter

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

AlexSavencu
Honored Contributor

Re: Portlet Filter

Hi,

 

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.

 

cheers

alex


--remember to kudos people who helped solve your problem
//Add this to "OnDomLoad" event