Brackets around $P!(UICFILTER2)) clause

[NCHASHIM]

My report SQL includes a clause

WHERE  ($P!{UICFILTER2}  )

where UCIFILTER2
is mapped to a query
SELECT domain.GENERATE_UIC_FILTER($P{LoggedInUsername}, 'ADHOC_HPIP_PERSONNEL,ADHOC_N31_ROLE',  'RESERVE_REPORTING_UNIT_CODE')  as RSVUIC_QUERY2 FROM DUAL

When I execute the report, I am receiving an error.  I noticed that the where clause reads

WHERE ([RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')])

What did these brackets [] come from? 

Does Jaspersoft require the use of the $X{} format in the SQL statement? 

Note:I actually want that second parameter to be LoggedInUserRoles and a hard-coded list of roles.  However, I am afraid that I would still get these brackets crash the sql statement.
 

 

 

[pawankumar.singh]

can you please post your query.

As you $P,$X is the different format paramter used for different purpose.

Thanks

Pawan

[NCHASHIM]

My first report query

select (pi.last_name || ', ' ||
        pi.first_name || ' ' ||
        pi.mid_initials) name,
       trim(pi.STREET_ADDRESS || NVL2(pi.STREET_ADDRESS_2, ' ' || pi.STREET_ADDRESS_2,'')) Address,
       trim(pi.CITY_ADDRESS || NVL2(pi.STATE_ABBREVIATION,', ' || pi.STATE_ABBREVIATION,'') ||
                          nvl2(pi.zip_code, ' ' || substr(zip_code,1,5),'')) City_State_Zip,
       trim(ud.EMAIL_ADDRESS) Email_Address,
       pi.reserve_reporting_unit_code ruic
FROM ows.personnel_info pi left join ows.user_data ud on ud.ssn = pi.ssn
WHERE $X{IN, reserve_reporting_unit_code, RUIC_LIST}                             
ORDER BY name

However, RUIC_LIST must generated dynamically.  It will be different for every operator based on their role. 

This code SHOULD be the query that populates RUIC_LIST, but Jasperserver choked on the regexp_substr statement

select distinct uicf.uic          
        FROM OWSADHOC.ADHOC_UIC_FILTER UICF
                 INNER JOIN OWSADHOC.ADHOC_USER_ROLE  UROLE
                    ON UICF.ROLE_NAME = UROLE.ROLE_NAME
                 inner join OWSADHOC.ADHOC_USER_DATA uname
                    on uname.USER_ID = UROLE.USER_ID
        WHERE uname.JASPER_ACCOUNT = P{LoggedInUserName}
        and UROLE.ROLE_NAME in (SELECT NAME
                                    FROM (SELECT regexp_substr(LoggedInUserRoles, '(.*?)(,|$)', 1, LEVEL, null, 1) NAME, LEVEL LVL
                                            FROM   DUAL
                                            CONNECT BY LEVEL <= regexp_count(LoggedInUserRoles, ',') + 1)
                                ) 

Therefore, I created a function that would generate the entire WHERE clause in standard SQL format.
I changed the report query to

select (pi.last_name || ', ' ||
        pi.first_name || ' ' ||
        pi.mid_initials) name,
       trim(pi.STREET_ADDRESS || NVL2(pi.STREET_ADDRESS_2, ' ' || pi.STREET_ADDRESS_2,'')) Address,
       trim(pi.CITY_ADDRESS || NVL2(pi.STATE_ABBREVIATION,', ' || pi.STATE_ABBREVIATION,'') ||
                          nvl2(pi.zip_code, ' ' || substr(zip_code,1,5),'')) City_State_Zip,
       trim(ud.EMAIL_ADDRESS) Email_Address,
       pi.reserve_reporting_unit_code ruic
FROM ows.personnel_info pi left join ows.user_data ud on ud.ssn = pi.ssn
WHERE $P!{UICFILTER)                            
ORDER BY name

 

The function definition is

CREATE OR REPLACE FUNCTION OWSADHOC.GENERATE_UIC_FILTER (P_LoggedInUserName IN owsadhoc.adhoc_user_data.jasper_account%TYPE, P_LoggedInUserRoles IN VARCHAR2, P_FIELDNAME IN VARCHAR2
) RETURN VARCHAR2  AS P_FILTER VARCHAR2(255);
/*****************************************************************************************
This procedures is used to generate the filter used on various adhoc jasper reports.
Version 0.00
Created by: Marian Moore
Date: 21 Oct 2019
Description: Initial Creation
*****************************************************************************************/
BEGIN
SELECT DISTINCT P_FIELDNAME ||' IN (' ||
                listagg ( chr(39) ||  uic || chr(39) , ',') within group (order by uic) || ')' 
into P_FILTER              
from ( select distinct uicf.uic          
        FROM OWSADHOC.ADHOC_UIC_FILTER UICF
                 INNER JOIN OWSADHOC.ADHOC_USER_ROLE  UROLE
                    ON UICF.ROLE_NAME = UROLE.ROLE_NAME
                 inner join OWSADHOC.ADHOC_USER_DATA uname
                    on uname.USER_ID = UROLE.USER_ID
        WHERE uname.JASPER_ACCOUNT = P_LoggedInUserName
        and UROLE.ROLE_NAME in (SELECT NAME
                                    FROM (SELECT regexp_substr(P_LoggedInUserRoles, '(.*?)(,|$)', 1, LEVEL, null, 1) NAME, LEVEL LVL
                                            FROM   DUAL
                                            CONNECT BY LEVEL <= regexp_count(P_LoggedInUserRoles, ',') + 1)
                                ) 
           
         )
      ;
RETURN P_FILTER;        

end  GENERATE_UIC_FILTER;
/

If I test this (using hard-coded values)
SELECT OWSADHOC.GENERATE_UIC_FILTER('MOORE_MARIAN', 'ADHOC_HPIP_PERSONNEL,ADHOC_N31_ROLE', 'RESERVE_REPORTING_UNIT_CODE') AS uicfilter FROM DUAL;
/

The function returns
RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')

Great!  However, when I execute the new function via Jasper, the Jasper report is still crashing.  When I look at the error, the clause is surrounded by brackets for some inexplicable reason.  It reads

[RESERVE_REPORTING_UNIT_CODE IN ('84251','8806N')]

           

[NCHASHIM]

Just to answer my own question.  This is how the query ended up.

SELECT OWSADHOC.GENERATE_UIC_FILTER($P{LoggedInUsername},$P{ROLE_LIST},   'RESERVE_REPORTING_UNIT_CODE')  as UICFILTER2 FROM DUAL

 

The SQL in the report reads

AND $P!{UICFILTER2}

AND $P{ROLE_LIST} IS NOT NULL

I had to define ROLE_LIST AS

String.join(",", $P{LoggedInUserRoles})